SOLVED

# Big dataset - 3 columns, xlookup, transpose, sort and unique

Copper Contributor

# Big dataset - 3 columns, xlookup, transpose, sort and unique

I have a spreadsheet consisting of

100.000+ rows and 3 columns with data.

Spoiler
 115HP 2 Stroke (15 Spline) 1978 - 1983 1411-130-19 1411-140-00 140HP 2 Stroke (15 Spline) 1978 - 1979 1411-130-19 1411-140-00 80HP 2 Stroke (15 Spline) 1977 - 1983 1411-130-19 1411-140-00 90HP 2 Stroke (15 Spline) 1978 - 1983 1411-130-19 1411-140-00 115HP 2 Stroke (15 Spline) 1978 - 1983 1411-130-21 1411-140-00 140HP 2 Stroke (15 Spline) 1978 - 1979 1411-130-21 1411-140-00 80HP 2 Stroke (15 Spline) 1977 - 1983 1411-130-21 1411-140-00 90HP 2 Stroke (15 Spline) 1978 - 1983 1413-128-17 1413-130-00 115HP 2 Stroke (15 Spline) 1978 - 1983 1431-130-19 1431-130-00

What i want to do is to search the "column A" value and return all matches from "column C"
The return values i want represented horizontally and i want it sorted and i only want to see unique values.

If i lookup value

"115HP 2 Stroke (15 Spline) 1978 - 1983"

i want the formula to return

"1411-140-00", "1431-130-00"
Thanks

2 Replies
best response confirmed by TelmoExcel (Copper Contributor)
Solution

# Re: Big dataset - 3 columns, xlookup, transpose, sort and unique

Try FILTER, UNIQUE and SORT formula.

=TRANSPOSE(SORT(UNIQUE(FILTER(C1:C9,A1:A9=E1))))

# Re: Big dataset - 3 columns, xlookup, transpose, sort and unique

Thank you, it works beautifully!
1 best response

Accepted Solutions
best response confirmed by TelmoExcel (Copper Contributor)
Solution

# Re: Big dataset - 3 columns, xlookup, transpose, sort and unique

Try FILTER, UNIQUE and SORT formula.

=TRANSPOSE(SORT(UNIQUE(FILTER(C1:C9,A1:A9=E1))))