Extract Unique values based from multiple columns

Hi

I have a work book that stores vehicle numbers (which is the unique value), these values are stored in three seperate columns on the same sheet


i have a formula that extracts unique values from a single column, but i dont know how to extract fro multiple columns

here is my code

Please Login or Register  to view this content.

Attach a sample workbook showing what you expect as your output.

thanks for the reply


i have attached a sample sheet


so on "dashboard" sheet, there is a table Called "reported vehicles", on "Data" sheet, there are three columns in a table called "vehicle_1", "vehicle_2" and "vehicle_3"

what i need to do is look at all three columns and display the unique values in "dashboard sheet table "reported Vehicles"

No attachment.

Go Advanced -> Manage Attachments -> Upload

apologies, i didnt notice the max upload size

Your sample doesn't have any sample data or what the output should look like.

sorry

there you go

This works for contiguous columns (so I copied Vehicle 1 to column Q) and it is unsorted.

in E12

=INDIRECT("Data!" &TEXT(MIN(IF((Data!$O$2:$Q$100<>"")*(COUNTIF($E$11:E11,Data!$O$2:$Q$100)=0),ROW($2:$100)*100+COLUMN($O:$Q),7^8)),"R0C00"),FALSE)&""

I think you will need to use VBA for non-contiguous columns plus sorting but others may be able to give you a formula solution.

Originally Posted by JohnTopley

This works for contiguous columns (so I copied Vehicle 1 to column Q) and it is unsorted.

in E12

=INDIRECT("Data!" &TEXT(MIN(IF((Data!$O$2:$Q$100<>"")*(COUNTIF($E$11:E11,Data!$O$2:$Q$100)=0),ROW($2:$100)*100+COLUMN($O:$Q),7^8)),"R0C00"),FALSE)&""

I think you will need to use VBA for non-contiguous columns plus sorting but others may be able to give you a formula solution.

hi


thanks for the reply


the reason its unsorted is, some of the data is imported from a text file, them copied to the table,

I simple said "Unsorted" as the formula does not sort.=: I don't know if a sorted list is required.

got it great stuff, thank you


one more question, is there any way of displaying just the filtered data in the table?

Originally Posted by JohnTopley

I simple said "Unsorted" as the formula does not sort.=: I don't know if a sorted list is required.

hey


i seem to be getting some odd values from the formula you provided. i cant upload the sample sheet as it wont let me