Multiple Columns Combined Into One New Unique Column
ExampleSpreadsheetForExperts.xls
Hello Experts,
File attached.
I've browsed through the existing solutions and couldn't find what I was looking for, so I thought I'd ask.
Many were close, but didn't quite get it done for me(i.e. =COUNTIF(B:B,A1)=0)
I'm trying to compile a column of unique items given the contents of the other columns in the worksheet. Forumula or Macro doesn't matter to me which (though I suspect it'll end up being a macro).
Additionally, I'd like them to remain in the order that they're in (this is the sticky part).
For instance, Plane, Ride, Walk, Sleep are the last 4 items in the rows and would need to remain so in the new Unique Column in the same worksheet.
PrussyCow, PrussyCow2 and PrussyCow3 are newly introduced and would need to be placed in unique order in the new column.
Items are text items (although some do have numbers in them, they aren't specific enough to sort on).
Clear as mud? Hope you can help.
Thanks
Hello Experts,
File attached.
I've browsed through the existing solutions and couldn't find what I was looking for, so I thought I'd ask.
Many were close, but didn't quite get it done for me(i.e. =COUNTIF(B:B,A1)=0)
I'm trying to compile a column of unique items given the contents of the other columns in the worksheet. Forumula or Macro doesn't matter to me which (though I suspect it'll end up being a macro).
Additionally, I'd like them to remain in the order that they're in (this is the sticky part).
For instance, Plane, Ride, Walk, Sleep are the last 4 items in the rows and would need to remain so in the new Unique Column in the same worksheet.
PrussyCow, PrussyCow2 and PrussyCow3 are newly introduced and would need to be placed in unique order in the new column.
Items are text items (although some do have numbers in them, they aren't specific enough to sort on).
Clear as mud? Hope you can help.
Thanks
Ignore my previous post. Not quite what you want.
Kevin
Kevin
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OK it's working, but I'm having trouble implementing it.
Since I couldn't implement it on my existing spreadsheet,
I tried it on the one you attached (which is how I know it's working), but I get a "More..." on line 50 in the Unique Items Column when replacing the example items with the items from my spreadsheet.
when trying to fix it, I get an error that you can change part of an array.
Since I couldn't implement it on my existing spreadsheet,
I tried it on the one you attached (which is how I know it's working), but I get a "More..." on line 50 in the Unique Items Column when replacing the example items with the items from my spreadsheet.
when trying to fix it, I get an error that you can change part of an array.
ASKER
Got it.
I needed to highlight the Unique Values column (J) and modify the bounds of the array
by hitting key SHIFT + CTRL + Enter, than locked it in by hiting enter again.
Just for clarification for others reading this later I highlighted column J all the way down to like line 300 (almost 3 times the length I'd thought I'd use).
I needed to highlight the Unique Values column (J) and modify the bounds of the array
by hitting key SHIFT + CTRL + Enter, than locked it in by hiting enter again.
Just for clarification for others reading this later I highlighted column J all the way down to like line 300 (almost 3 times the length I'd thought I'd use).
ASKER
The solution was complete and accurate and fast. Implementation instructions was a little off, but I got through it
Name the source list "List" and the first cell in that list "ListFirstCell". Name the unique list "UniqueList". The unique list should contain the same number of cells as the source list. Name the first cell in that list "UniqueListFirstCell" and place this array formula in that cell:
=IF(SUM((FREQUENCY(IF(List
To enter an array formula press CTRL+SHIFT+ENTER. Copy the formula down to the end of the list.
Note that the values included can be conditional. To use a condition such as greater than some value replace the two occurances of "List<>""" with the condition.
Kevin