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

#### Novellas

##### New Member
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>

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### oldbrewer

##### Well-known Member
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.

##### MrExcel MVP
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:
Rich (BB code):
``=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:
Rich (BB code):
``````=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

##### New Member
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

##### Well-known Member
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

##### MrExcel MVP
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>

Rich (BB code):
``````=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

##### New Member

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

##### New Member
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?

##### MrExcel MVP

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

Last edited:

#### Novellas

##### New Member
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

Replies
6
Views
621
Replies
3
Views
235
Replies
7
Views
418
Replies
7
Views
1K
Replies
0
Views
198

1,195,924
Messages
6,012,336
Members
441,691
Latest member
starlightmuse

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back