LOOKUP and Find Next Unique Value

[excel2425]

I've attached an example of how my data is organize. I'm looking to enter formulas in cells of column C on sheet2 which reference a single cell in column B which it then looks for in sheet 1 and finds the next un-used value in the same row:column C. The data is not organized nicely and I would like to only have to enter a single letter in column B on sheet2 and have all the information in coumn C populate. Having the information in column C(sheet2) alphabetical or smallest to largest is not important. Basically I'm just looking to have it start in row 2 on sheet1 and cycle down and fill in the information that way. I'd like to do it with a formula because the quantity of rows on sheet2 varies and where I enter "A", "B" etc...but if it must be VBA I'd like to note that there will be other information running down in column B(sheet2) which is not relevant to the data which must stay. Any help in sorting out a lookup/match/index etc formula would be greatly appreciated!

[oeldere]

maybe with a pivot table

[excel2425]

Thanks for the tip @oeldere! Unfortunately I have it all in colorful rows and formatting which I cannot replicate with a pivot table and why I've been doing everytihng by hand and wasting a lot of time so I figured there's probably a formula out there hahah!

[excel2425]

Maybe theres a way to use the "GETPIVOTDATA" formula?

[oeldere]

IN that case I don't understand your (fully) request.

[excel2425]

Sorry for any confusion. I was thinking of producing a pivot table on another sheet then use that formula to search the pivot table and potentially find the proper information. I'm not totally sure if its possible.

[excel2425]

Would it be possible to put a formula for example in cell C6 to reference B1 and look for it on sheet1 then once found grab the value in the same row of column C then paste it if it is not currently in sheet2 C6:C13?

[Czeslaw]

And if we apply the AGGREGATE function and drop-down list.

[excel2425]

So when I change the drop down menu I'm not seeing the values change. Could I be using a different version of Excel (2007)? Also, I appologize that maybe I didnt mean for it literally to say "Should Be", rather just the numbers i.e. 30,21,32,33...etc

[Czeslaw]

Added version for Excel 2007.

[excel2425]

I think its 95% of what I'm looking for. I'll need to have it repeated in random orders down the sheet and I'm getting blank when I tried?

[Czeslaw]

Once again, see the Attached Files.
In all cases, the first formula should be of this form.

=IFERROR("SHOULD BE:  "&SMALL(INDEX(Sheet1!C$2:C$23*(Sheet1!E$2:E$23=B$16),0),COUNTIF(Sheet1!E$2:E$23,"<>"&B$16)+ROW(1:1)),"")

[excel2425]

Thanks Czselaw! So I've tried it on my actual sheet where I have the more confidential information and its not quite working...Does this not work if I enter the formula in a merged cell? Also what should the array be?

[excel2425]

I've actually determined the reason the formula isnt working is becasue in the actual sheet I have values with - in them such as F-1-101A. Is there a way for the formula to still work with values like this in column C on sheet1?

[Czeslaw]

Attach an example.

[excel2425]

Please see attached

[JohnTopley]

Try

=IFERROR(INDEX(Sheet1!C$2:C$23,SMALL(IF(Sheet1!E$2:E$23=B$1,ROW($A$2:$A$23)-ROW($A$2)+1,""),ROWS($A$2:A2))),"")

Enter with Ctrl+Shift+enter

[Czeslaw]

Please see the result.

[excel2425]

I have one last question I thought of for down the road. I think I might likely be entering this forumla in a merged cell. From my previous experience I know that array formulas dont work and wondering how I'd modify this in order for the array to work?

[oeldere]

I think I might likely be entering this forumla in a merged cell

.

My advice is NOT to work with merged cells, you get in trouble with it sooner or later.