LinkBack URL
About LinkBacks
Hi
I have a data in multiple columns with some repetitive values and i want to collate unique values (not duplicates) in a single column from these multiple columns.
Can somebody please help me with excel or VB.
how to collate list of unique values from multiple columns into a single column
Hi
I have a data in multiple columns with some repetitive values and i want to collate unique values (not duplicates) in a single column from these multiple columns.
Can somebody please help me with excel or VB.
This should work
HTML Code:
Thanks for your help!! is there something that can be done through excel formula
Can the Data in Col 1,2,& 3 be placed into 1 column?
There's an example of a formula method here:
http://www.excelforum.com/excel-form...le-column.html
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
I have these columns with huge data may be each column with 3000 row items, so it would be difficult to place them in single column. also it is associated with other data points.
The macro below.
After that data => remove duplicates on column N.
Please Login or Register to view this content.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Great!! this answers my query however i have another question to it, how do i get same result if i have data not placed in adjacent columns like A, D, F etc..
Here is formula solution:
Enter this array formula (Ctrl+Shift+Enter) in F2
=IFERROR(IFERROR(IFERROR(INDEX($B$2:$B$4, MATCH(0, COUNTIF($F$1:F1, $B$2:$B$4)+($B$2:$B$4=""), 0)), INDEX($C$2:$C$4, MATCH(0, COUNTIF($F$1:F1, $C$2:$C$4)+($C$2:$C$4=""), 0))), INDEX($D$2:$D$4, MATCH(0, COUNTIF($F$1:F1, $D$2:$D$4)+($D$2:$D$4=""), 0))), "")
Col 1 Col 2 Col 3 Result Apple Banana Lemon Apple Orange Lemon Apple Orange Lemon Banana Orange Lemon Banana
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
If the columns to evaluate followed a specific pattern then it could be done.
Your columns: A, D, F etc.., do not follow a set pattern: A=1, D=4, F=6.
Consider this example:
Data Range
Enter this array formula** in A8. This will return the count of the unique items in the range.
=SUM(IF(MOD(COLUMN(A2:E5),2)=1,IF(A2:E5<>"",1/COUNTIF(A2:E5,A2:E5))))
Enter this array formula** in B8 and copy down until you get blanks.
=IF(ROWS(B$8:B8)>A$8,"",INDEX(A$2:E$5,MIN(IF(MOD(COLUMN(A$2:E$5),2)=1,IF(A$2:E$5<>"",IF(ISNA(MATCH(A$2:E$5,B$7:B7,0)),ROW(A$2:E$5)-ROW(A$2)+1)))),MOD(MIN(IF(MOD(COLUMN(A$2:E$5),2)=1,IF(A$2:E$5<>"",IF(ISNA(MATCH(A$2:E$5,B$7:B7,0)),(ROW(A$2:E$5)-ROW(A$2)+1)*10^5+(COLUMN(A$2:E$5)-COLUMN(A$2)+1))))),10^5)))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Last edited by Tony Valko; 10-14-2013 at 08:03 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks