Creating a column from multiple columns, with conditions

Simon20447

I have created a sheet which is populated via a form for the auditing of stock.

Each audit consists of 20 items. I'd like to create a separate sheet that only lists the items that have discrepancies.

Sheet looks something like this…

Would like the Discrepancy Sheet to look like this:

So, the audit number, item number and difference are moved to the new sheet when the difference is NOT 0…

Any help would be great appreciated!

Cheers

Find more posts tagged with

Formulas

columns

copy

Accepted answers

All comments

kowal

Hi Simon,

I woud create a new workflow that is triggered by adding new row.

I would set a condition Difference "is not equal" to 0

and if this criteria is met copy this row to new sheet.

Does this help?

Simon20447

Hi Tomasz,

I may not have described what I am looking for very well. I do not want to copy the whole row, just specific columns in a row when the difference is not 0.

So if Item 1 (item number 123456) difference is -1, then I want Audit Number, Item Number 1, and Difference 1 for that row to be moved.

If the first sheet has 20 items that have been audited, in 1 row, and there is only a difference in item 17, i only want the details of that item and the difference to be copied over.

Cheers

Courtney S.

Although it will be a lot of work to set up upfront, I think my first step to solve this situation would be to create an entire "helper" sheet that uses cross-sheet references to re-format one row of the original "one row per audit" sheet into a table that has one row per item #. So, the new sheet would look something like the below. (I just typed in the audit # in the Item# row 1 which is a parent row, then the audit # got filled into the Audit Number column for the child rows, the items). You'd want to list all possible item numbers in the rows, or use 20 formulas with cross-sheet references to pull in the 20 item numbers from the original sheet's row for the audit.

The "Difference" column would need to be filled in using some combination of formulas to lookup the specific Difference answer for that specific audit row and item number. I expect it would have to use MATCH/INDEX somehow to get the particular Column/Row for the correct audit+item combination, then offset the value 3 columns to the right to collect the actual Difference answer. That formula is a little beyond me currently.
OR: if you set it up so the first item row in this sheet always pulls the item number from the first item's column, then the difference column could similarly jsut be set to pull the answer from the first Difference column, and so on. So, you'd need to set up 20 cross sheet references for the 20 item number answers, then next to them, set up 20 cross sheet references for the 20 difference answers. But if you set up the formulas so that they use the audit number in the Parent row to determine the correct row in the original sheet, you only have to built those 40 formulas once. then you can change your audit number in the parent row to have a different audit's answers filled in.

Then you can simply filter to the rows with a non-zero difference, or have those rows copy themselves to a new sheet.

Almonto1

Hi there!

I would start by making a helper column in the source sheet with an IF formula (i.e., =IF(Difference@row = 0, "No", "Yes").

Then I would use the "Copy row when specified criteria are met" automation to copy rows into your discrepancy sheet when the helper column is "Yes"

Let me know if that helps!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.