Sum-ifs or v-lookup question: return one value per multiple/varying dates

[Novellas]

I would like to have a formula that would sort through the Location & Date data, and for every unique date as it applies to a location, return to a new sheet a single location value; For Ex: for Paris, it shows 3 dates (2/1), I'd like for the formula to return one Paris even though are 3 corresponding dates (just want one instance of the location per unique date, even if multiple dates).

I was thinking v-lookup, but having no luck.

Thanks for any help.

Location	Date
Paris	02/01/2013
Paris	02/01/2013
Paris	02/01/2013
Germany	02/01/2013
Germany	02/01/2013
Germany	02/01/2013
Germany	02/02/2013
Germany	02/03/2013
Germany	02/03/2013
Texas	02/05/2013
Texas	02/06/2013

<tbody>
</tbody><colgroup><col><col></colgroup>

 

[Aladin Akyurek]

...
You had mentioned there might be a quicker way to do this?--how's that?

Sheet1, A:B...

Location	Date
Paris	2/1/2013
Paris	2/1/2013
Paris	2/1/2013
Germany	2/1/2013
Germany	2/1/2013
Germany	2/1/2013
Germany	2/2/2013
Germany	2/3/2013
Germany	2/3/2013
Texas	2/5/2013
Texas	2/6/2013

<colgroup><col style="width: 97pt; mso-width-source: userset; mso-width-alt: 4579;" width="129"> <col style="width: 85pt; mso-width-source: userset; mso-width-alt: 4010;" width="113"> <tbody>
</tbody>

Sheet2, A:C, the processing...

A2, just enter, copy across to B2, and down:

=IF($C2="","",INDEX(Sheet1!A$2:A$12,$C2))

C2, control+shift+enter and copy down:

=IFERROR(SMALL(IF(FREQUENCY(IF(Sheet1!$A$2:$A$12&"|"&Sheet1!$B$2:$B$12<>"", MATCH(Sheet1!$A$2:$A$12&"|"&Sheet1!$B$2:$B$12,Sheet1!$A$2:$A$12&"|"&Sheet1!$B$2:$B$12,0)),ROW(Sheet1!$A$2:$A$12)-ROW($A$2)+1),ROW(Sheet1!$A$2:$A$12)-ROW(Sheet1!$A$2)+1), ROWS(C$2:C2)),"")

This set up uses the array formula just once.

 

[Novellas]

Thanks for all the help Aladin--problem solved and thanks for the education!

 

[Aladin Akyurek]

Thanks for all the help Aladin--problem solved and thanks for the education!

You are welcome. Thanks for providing feedback.

 

[oldbrewer]

You can go to tools, macros, record new macro and then just do what you want to do. But this is a mathematical macro and so I wrote it from scratch. If you have never done BASIC programming it will be hard for you. I am willing to correspond off forum - PM me your Email if you want to.

 

[Novellas]

Aladin,

Regarding the formula above, I am having problems with it generating data beyond say 145 (even with more combinations present).

I've updated the array range, but when I copy across and drag down, it only returns say 20 or so data figures and then the rest are blanks...I'm not sure why this is.

The formual is present in these cells, but nothing is returned.

Any suggestions?

 

[Aladin Akyurek]

Aladin,

Regarding the formula above, I am having problems with it generating data beyond say 145 (even with more combinations present).

I've updated the array range, but when I copy across and drag down, it only returns say 20 or so data figures and then the rest are blanks...I'm not sure why this is.

The formual is present in these cells, but nothing is returned.

Any suggestions?

Which set up did you use, that is, the one with concatenation or not?