Extract Unique Values Based On Criteria In Google Sheets

0
votes

I have a Google Spreadsheet where I'm trying to get a formula which returns the 'matches' under Visit 1, Visit 2 etc headings.

In the simplified data, we have site ID and onsite date. The same Site ID and date may appear multiple times and or a site ID may appear multiple times under different dates.

For each store number, I want to display unique visit dates only. I have a formula in sheet "Main" column D which returns the first "Visit 1" date, but in column F, I want it to show the second date which matches the site ID. If there is no match, then return "No Visit".

Sheet "Main" Fixed Site ID = Column A

Sheet "Data" Site ID = Column A Date = Column D

The current formula I have is:

=ArrayFormula(IFERROR(INDEX(Data!$D$2:$D,MATCH(1,INDEX(COUNTIF($A$2:$A2,Data!$D$2:$D) + (Data!$A$2:$A=$A2),),0)),"No Visit"))

Link to example spreadsheet below. The final result for Visit 2 Date should show site ID 1111 date as 5/5/17 and Site ID 5555 as 5/5/17 :

https://docs.google.com/spreadsheets/d/1Rk3KCbF_r29st3eFSazNEUKK0ow6dbQ3G9wLGmnjM60/edit?usp=sharing

Thanks.

1
arraysgoogle-sheetsmatchreturn-valueformula

1 Answers

0
votes

Figured it out. Modified the formula slightly.

First visit column now has formula:

=ArrayFormula(IFERROR(INDEX(Data!$D$2:$D$7, MATCH(0, IF($A2=Data!$A$2:$A$10, COUNTIF($D1, Data!$D$2:$D$10), ""), 0)),"No Visit"))

Second and subsequent visit columns can use:

=ArrayFormula(IFERROR(INDEX(Data!$D$2:$D$7, MATCH(0, IF($A2=Data!$A$2:$A$10, COUNTIF($D1:$E2, Data!$D$2:$D$10), ""), 0)),"No Visit"))