0

I have a spreadsheet of data that has a column for "Cost Category" and a column for "Vendor Name" among others. I need to create a list of all the unique vendors that fall into the cost category of labor.

This question is partially answered by the question at the following link: Ignore Duplicates and Create New List of Unique Values in Excel

The formula in that link creates a list of unique vendor names but I cannot figure a way to make the formula only source from vendor names that fall into the Labor cost category.

I need to accomplish this with a formula rather than with VBA because results in the new list will be used to populate other sheets within the document to drill down into additional data.

Thanks for the help!

Update: I've included a link to a google doc with a set of sample data. I hope this helps. https://docs.google.com/spreadsheets/d/1SofyLcIxnglQgojNP7YxjkS9kJrfp35qA2Y7nE2PZoo/edit?usp=sharing

Community
  • 1
  • 1
jeffbartelli
  • 11
  • 1
  • 6
  • Can you post a sample of the two columns? Either type a few rows or better still a link to a screen shot possibly stored on a web link (imgur maybe?) Or even in Google docs? – fraggle Dec 10 '14 at 21:13

1 Answers1

1

The way I approached this may or may not work for you. Basically, I did the following:

  1. Create a column called index which creates a numerically incremented list of all the items that match the criteria (Column C).
  2. Create a column that uses =VLOOKUP() or =INDEX(MATCH()) to retrieve the value of each item where the index number is found for the current row (Column E).

I created a sample sheet as in the screenshot below: Screenshot of spreadsheet

The formulas used are:

C4 =IF(A4=C$2,MAX(C$3:C3)+1,0)
D4 =D3+1
E4 =INDEX(B:B,MATCH(D4,C:C,0),1)
F4 =IFERROR(E4,"")

I just dragged the formula down for each column.

neelsg
  • 4,802
  • 5
  • 34
  • 58
  • neelsg, your solution works though it forces me to use far more columns than I would have liked (a total of 4 vs 1). I truncated your formulas for columns E and F into a single formula:`[=IFERROR(INDEX(B:B,MATCH(D4,C:C,0),1),"")` I then took the formula from [link](http://stackoverflow.com/questions/13307927/ignore-duplicates-and-create-new-list-of-unique-values-in-excel) for deriving a list of unique values. This produced the desired end result. – jeffbartelli Dec 15 '14 at 17:13
  • Glad it works for you. I only added the multiple columns for easier understandibility – neelsg Dec 18 '14 at 18:12