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>

 

[oldbrewer]

Insert a third dummy column that checks if A2=A1 (paris in both cells) and B2=B1 (same date in both cells). If both are true put zero in C3. copy down the table. In your new sheet, have a formula that grabs the values in col A if the value in Col C is non zero. You will then need to sort the new data to remove blanks and re=instate the formulas for the next time you run it.

 

[Aladin Akyurek]

Sheet1, A:B, houses the data.

Sheet2, A:B, the processing...

Loc	Date
Paris	1-Feb-13
Germany	3-Feb-13
Texas	6-Feb-13

<colgroup><col style="width: 48pt;" width="64"> <col style="width: 58pt; mso-width-source: userset; mso-width-alt: 2759;" width="78"> <tbody>
</tbody>

A2, just enter and copy down:

=IF(B2="","",INDEX(Sheet1!$A$2:$A$12,MATCH(B2,Sheet1!$B$2:$B$12,0)))

B2, control+shift+enter, not just enter, and copy down:

=IFERROR(SMALL(IF(1-ISNUMBER(MATCH(Sheet1!$B$2:$B$12,$B$1:B1,0)),
  Sheet1!$B$2:$B$12),ROWS($B$2:B2)),"")

 

[Novellas]

Thanks guys. ONe thing though, I was imagining the ans would be similar to this (like deleting duplicates, but auto-accessing when dealing with large amounts of data):

Any suggestions?

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

<tbody>
</tbody>

 

[oldbrewer]

this basic macro gives you what you want as defined in your previous post of 05:23 pm (assumes data in col A and B) It prints on same page at present but you can easily add a different sheet name to the macro.


tot = 40
Cells(40, 1) = Cells(1, 1)
Cells(40, 2) = Cells(1, 2)
For j = 2 To 30
If Cells(j, 1) = Cells(j - 1, 1) Then GoTo 10
tot = tot + 1
Cells(tot, 1) = Cells(j, 1)
Cells(tot, 2) = Cells(j, 2)
GoTo 30
10 If Cells(j, 2) = Cells(j - 1, 2) Then GoTo 30
tot = tot + 1
Cells(tot, 1) = Cells(j, 1)
Cells(tot, 2) = Cells(j, 2)
30 Next j
End Sub

 

[Aladin Akyurek]

Thanks guys. ONe thing though, I was imagining the ans would be similar to this (like deleting duplicates, but auto-accessing when dealing with large amounts of data):

Any suggestions?

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

<tbody>
</tbody>

Sheet1, A:B, the data...

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

<tbody>
</tbody>

Sheet2, A:B, the processing...

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

<colgroup><col style="width: 68pt; mso-width-source: userset; mso-width-alt: 3242;" width="91"> <col style="width: 67pt; mso-width-source: userset; mso-width-alt: 3157;" width="89"> <tbody>
</tbody>

=IFERROR(INDEX(Sheet1!A$2:A$12,
  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($A$2:A2))),"")

We can have this in a faster set up if so desired.

 

[Novellas]

Aladin,

When I input the formulas I'm getting this outcome (not sure what I'm doing wrong?):

Paris	2/1/2013
Germany	2/3/2013
Germany	2/6/2013
Germany

<colgroup><col style="width: 48pt;" span="2" width="64">
<tbody>


</tbody>

The formulas I am using (placed in A2 & B2,
sheet2), are below:

A2:

=IFERROR(INDEX(Sheet1!A$2:A$12,

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($A$2:A2))),"")



B2:

=IFERROR(SMALL(IF(1-ISNUMBER(MATCH(Sheet1!$B$2:$B$12,$B$1:B1,0)),

Sheet1!$B$2:$B$12),ROWS($B$2:B2)),"")

 

[Novellas]

Think I figured it out; I just manipulated the formula in B2 as follows:

=IFERROR(INDEX(Sheet1!B$2:B$12,
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($A$2:A2))),"")


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

 

[Aladin Akyurek]

Aladin,

When I input the formulas I'm getting this outcome (not sure what I'm doing wrong?):...

A2, control+shift+enter, copy across, and down:

=IFERROR(INDEX(Sheet1!A$2:A$12, 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(A$2:A2))),"")

See:
https://dl.dropboxusercontent.com/u/65698317/aaUniqueDatesAndTheirAssociates Novellas version-2.xlsx

 

[Novellas]

old brewer,
I'm a novice when is comes to using macros--how do you go about using them;
I'm under no illusion that this is "involved" to say the least, but what you recommend as far as getting started.
I know how powerful they can be