1

I have a table, something like this:

--------------------------------------------------------------------------------------
|Id | Name                    | Category | Sub-Category     | Supplier
--------------------------------------------------------------------------------------
| 1 | Orange juice "TASTY"    | Beverage | Juice            | Le Beverage supplier
--------------------------------------------------------------------------------------
| 2 | Canned tuna "LUNA TUNA" | Food     | Canned           | Food Supplier
--------------------------------------------------------------------------------------
| 3 | Pepsi, just Pepsi       | Beverage | Soft Drink       | Le Beverage supplier
--------------------------------------------------------------------------------------
| 4 | Fanta, just fanta       | Beverage | Soft Drink       | Le Beverage supplier
--------------------------------------------------------------------------------------
| 5 | French Fries, frozen    | Food     | Frozen Food      | Food Supplier
--------------------------------------------------------------------------------------
| 6 | Pepsi, just Pepsi       | Beverage | Soft Drink       | Other Beverage Supplier
--------------------------------------------------------------------------------------
| 7 | Fanta, just fanta       | Beverage | Soft Drink       | Other Beverage Supplier
--------------------------------------------------------------------------------------
| 8 | Dog Food                | Pet Food | Tasty            | Best Dog Food Inc.
--------------------------------------------------------------------------------------

Category and sub-category columns are initially missing all the values, all of them are entered manually based on the item name itself and common sense. So the main goal is to enter them as efficiently and quickly as possible. We've tried filtering by supplier; that allowed us to work most likely within one category (there are 500 of them) and save some time, but then there were duplicates with the same name from other suppliers that didn't get covered – e.g., Id 3, 4 and 6, 7. Then we've tried filtering by Name, this solves the problem with duplicates but forces us to work outside one supplier so we constantly switch between categories.
My questions are:

  1. Is there a way to update all duplicates automatically when we use filtering by supplier? By update I mean automatically assign a category and sub-category to all columns with the same name but different supplier. Example case would be:

    • set category X and Sub-Category Y to row with Id 6 when I set row 3 category to X and Sub-Category to Y because they have the same name.
  2. Is there any good alternative way of doing that beside the way we try to do it now? I almost feel that there is some kind of magic tool in Excel that was designed to help with tasks specifically like this one.

  • 1
    Please try harder to explain what you are doing and what you want to do in clear, plain English.  Don’t imagine that you’re talking to your boss or a colleague; imagine that you’re talking to somebody who knows Excel but doesn’t know about your business. Please do not respond in comments; edit your question to make it clearer and more complete. – G-Man Jan 22 '18 at 20:54
  • 1
    More specifically from @G-Man, what exactly gets updated? You say "update all duplicates", but update what? Are you updating column Z with a new number? Where did this new number come from? – K.Dᴀᴠɪs Jan 22 '18 at 20:59
  • question updated a little bit, hope this clarifies the case – HardLuck Jan 22 '18 at 21:03
  • Just an unrelated observation that may simplify your work: the category can be found from the sub-category. You can save data entry by using a lookup table for category and populating that automatically. – fixer1234 Jan 22 '18 at 21:38
  • To your question, why not just search the table on product name? For sub-category (and category), prepopulate the table with a lookup, returning the value from a previous entry if it exists. If nothing is found, enter it manually, replacing the lookup formula. You don't really need to deal with the supplier for that. – fixer1234 Jan 22 '18 at 21:44
2

I’ll assume that the data you have shown are cells A1:E9.  Enter

=IFERROR(VLOOKUP($B3, $B$2:$D2, 2, FALSE), "")

into cell C3.  Drag/fill it to the right, to cell D3, and change the last 2 (the one just before the FALSE) to 3, so it looks like this:

=IFERROR(VLOOKUP($B3, $B$2:$D2, 3, FALSE), "")

Then select cells C3:D3 and drag/fill down as far as you need.

Now enter data the way you normally (currently) do.  (If you have some mechanism to generate the “Id” value in Column A, that should not be affected.)  You will, naturally, need to type “Category” and “Sub-Category” values into cells C2:D2, since they can’t be duplicates of anything.  Subsequently, when you tab into Columns C and D, you will see the above formulas in the formula box.  Just ignore them and type new “Category” and “Sub-Category” values.

Until you enter a duplicate value in Column B.1  Then, when you complete that data entry (either by pressing Ctrl+Enter or by tabbing into Column C), the “Category” and “Sub-Category” values for the first row with that product name will automatically appear in Columns C and D.  If they are what you want, just tab to Column E and continue as usual.  If they’re not what you want, just ignore them and type the desired values into Columns C and D.

Illustration:

Here I’ve started to type “Pep” into cell B7, and Excel suggests “Pepsi, just Pepsi” because that was entered in cell B4:

   before

I press Tab to accept the suggestion, and Excel fills in cells C7 and D7 with the “Beverage” and “Soft Drink” values from cells C4 and D4.

   after

These formulas are fairly simple.  VLOOKUP searches for data in a vertical data set (i.e., a column).  My formulas look for the value in Column B in the current row (i.e., the product name) in Column B above the current row.  If an exact match is found, the formulas show the value from Column C of the (first) matching row in Column C of this row, and likewise for Column D.

If you want to make the automatic values permanent, copy and paste values.  You should do this before sorting the data.
________
1 On my system (running Excel 2013), Excel will notice when I’m starting to type a duplicate value and will suggest the value that I’m duplicating.  If your system does this, just press Tab to accept the suggestion.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.