Extracting Data based on Equal Cell Values

[JRQ]

Hello! I have been losing sleep trying to figure out the right
formula/tool for this problem.

I would like to extract the data from cells in Column A when the values
in Column B cells are equal so that the final sheet only has unique
values in Column B with corresponding values in Column A.

example:

Column A Column B
bat 1
ball 2
dog 2
cat 2
red 3
blue 3
texas 4

Final result will look like

Column A Column B
bat 1
ball, dog, cat 2
red, blue 3
texas 4

Is this possible?

 

[Ken Wright]

Take a look at Advanced Filter / Unique records only. Debra Dalgleish has a good intro on her
site:-

http://www.contextures.com/xladvfilter01.html

Look at the 'Filter Unique records' section

 

[JRQ]

Thank you for the link. However, I have a spreadsheet with about 1,000
rows that I need to filter where there are more than 500 unique values
in Column B. the advanced filter wouln't work in this case.

 

[Ken Wright]

Why??

Straight from the link I gave you:-

Filter Unique Records

You can use an Advanced Filter to extract a list of unique items in the database. For example, get
a list of customers from an order list, or compile a list of products sold:

Select a cell in the database.
From the Data menu, choose Filter, Advanced Filter.
Choose 'Copy to another location'.
For the List range, select the column(s) from which you want to extract the unique values.
Leave the Criteria Range blank.
Select a starting cell for the Copy to location.
Add a check mark to the Unique records only box.
Click OK.

 

[JRQ]

This allows me to extract unique values in Column B, but it is the
correspoding values in Column A that I need.

So the final result will have all values in Column A that have equal
values in Columb B adjacent to that unique value in Column C.

All values in column B will be unique with the values from colum A
extracted and in adjacent cells.

Know what I mean?

 

[Ken Wright]

Sorry - I only just read the example you gave - AF will not do that as you have surmised.

 

[JRQ]

no worries. this might have to involve some type of macro which I have
no clue how to do. sigh Hopefully, I'll come across a solution soon.

 

[Ken Wright]

OK - Kludgy as hell I'm afraid, but it works:-

With your data in say A1:B100 with headers in A1:B1. In C2 put the following formula and copy
down to C100:-

=COUNTIF($B$2:B2,B2)

In D2 put =A2 and in D3 put =IF(C3>C2,D2&","&A3,A3) and copy down to D100

In E2 put =IF(LEN(D2)>=LEN(D3),D2,"") and copy down to E100

This should give you what you need in Col E. Now select Col E and do Edit / Copy, then Edit /
Paste Special / Values. If you don't want to lose your original data, then make a copy of it.

Delete Cols B / C / D

In what is now Col C, in C2 put =IF(B2="","",ROW()) and copy down. Copy Col C and paste special
as values.

Select A2:C100 and sort on Col C, then delete Col C and any surplus data in Col A.

 

[JRQ]

OH MY GOODNESS - You're a genius - if I could carry you around in my
pocket I would. Thank you so so much. This is saving me hours because
it is a function I'll have to use often. You've made my entire month!



Thanks again Ken!

 

[Ken Wright]

LOL - just wish it had been a bit neater, but glad you are sorted anyway.