Solving for data issue when exporting data from external source.
I am trying to create a helper column that will help a colleague manage data that he needs to export from our inventory management system to evaluate in a team meeting. The issue is that he has currently been manually fixing a common data error where, occasionally, the name of an item is exported incorrectly. It always repeats the same exact issue and I was hoping I could solve for it to save him the extra work. Below are a list of the steps he is currently following to make the change.
- Search "Item Description" for the phrase "~Flower Lot - QA"
- Find another item description with a matching "Batch" and replace "~Flower Lot - QA" with the correct Item Description.
My thought was I could create a separate "Item Description" column that would use an IF statement to look for the incorrect item description, but I am not sure how I can pull the corrected one... index match is not an option since it might accidentally pull the wrong data...
I hope this all makes sense. I just want to make his job a bit easier. Below is a screenshot example of a correct vs incorrect item description. Hoping someone has a suggestion for a column for a different column that can fix the issue.
(Also forgive the example Item Description... I work in cannabis so our products have weird names.)
Best Answer
-
I ended up answering my own question if anyone ends up finding this with a similar question. I created a new column called "Corrected Item Description" and wrote the following formula:
=IF(CONTAINS("~Flower Lot", [Item Description]@row), INDEX(DISTINCT(COLLECT([Item Description]:[Item Description], [Item No.]:[Item No.], [Item No.]@row)), 1), [Item Description]@row)
What I knew about the problematic data:
- It always contained the same piece of text: ~Flower Lot
- It was always the last line of data for a particular Item Number that was impacted
What the formula does in paragraph structure: It looks for the faulty string of text and if it finds it, it looks for the first Item Description associated with that item number and inputs that value. If it doesn't find the faulty string of text it just displays the item description associated with that line of text.
Answers
-
I ended up answering my own question if anyone ends up finding this with a similar question. I created a new column called "Corrected Item Description" and wrote the following formula:
=IF(CONTAINS("~Flower Lot", [Item Description]@row), INDEX(DISTINCT(COLLECT([Item Description]:[Item Description], [Item No.]:[Item No.], [Item No.]@row)), 1), [Item Description]@row)
What I knew about the problematic data:
- It always contained the same piece of text: ~Flower Lot
- It was always the last line of data for a particular Item Number that was impacted
What the formula does in paragraph structure: It looks for the faulty string of text and if it finds it, it looks for the first Item Description associated with that item number and inputs that value. If it doesn't find the faulty string of text it just displays the item description associated with that line of text.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- 10.7K Get Help
- 63 Global Discussions
- 68 Industry Talk
- 385 Announcements
- 3.5K Ideas & Feature Requests
- 55 Brandfolder
- 125 Just for fun
- 50 Community Job Board
- 464 Show & Tell
- 40 Member Spotlight
- 44 Power Your Process
- 28 Sponsor X
- 234 Events
- 7.3K Forum Archives
Check out the Formula Handbook template!
