SOLVED

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

Copper Contributor

I have a spreadsheet consisting of 

100.000+ rows and 3 columns with data.

Spoiler
Spreadsheet example
115HP 2 Stroke (15 Spline) 1978 - 19831411-130-191411-140-00
140HP 2 Stroke (15 Spline) 1978 - 19791411-130-191411-140-00
80HP 2 Stroke (15 Spline) 1977 - 19831411-130-191411-140-00
90HP 2 Stroke (15 Spline) 1978 - 19831411-130-191411-140-00
115HP 2 Stroke (15 Spline) 1978 - 19831411-130-211411-140-00
140HP 2 Stroke (15 Spline) 1978 - 19791411-130-211411-140-00
80HP 2 Stroke (15 Spline) 1977 - 19831411-130-211411-140-00
90HP 2 Stroke (15 Spline) 1978 - 19831413-128-171413-130-00
115HP 2 Stroke (15 Spline) 1978 - 19831431-130-191431-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

@TelmoExcel 

Try FILTER, UNIQUE and SORT formula.

 

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

 

Harun24HR_0-1658827304523.png

 

Thank you, it works beautifully!
1 best response

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

@TelmoExcel 

Try FILTER, UNIQUE and SORT formula.

 

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

 

Harun24HR_0-1658827304523.png

 

View solution in original post