Hi folks,
Newbie here and wanted to hear your expertise on this;
Per table attached., I wanted to have this summarized in one column (same as highlighted), any duplicate must be removed and listing should not include blanks. Assuming it’s a list of thousands of rows and should be listed in a one column list, what should I do?
Please.
1) Delete the blank row at the top.
2) Put this array formula in F2:
=INDIRECT(TEXT(MIN(IF(($A$2:$E$10<>"")*(COUNTIF($F$1:F1,$A$2:$E$10)=0),ROW($2:$10)*100+COLUMN($A:$E),7^8)),"R0C00"),)&""
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
Once that array formula is correctly entered, copy down until you start seeing blanks.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
hI jB, thanks for the feedback and immediate response. I have issue if the data are too long, it doesnt work properly unlike the one in an example.
Or is there any other way, of doing it in a simple manner?
What problems? How much data?
If it's a lot of data, I would use VBA to extract the unique values programmatically.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks