Listing multiple column entries into a single column

**roh710** · 05-31-2013, 04:04 PM

Hello everyone!

I am new to the forum and I hope someone will point me into the right direction.

I have multiple column entries (A1:C3) and I'd like to list the entries in a single column (E) without duplicates (Please see screen shot):

EXP.png

Does anyone have possible solution to this?

Thank you in advance!

KR

**Tony Valko** · 05-31-2013, 04:46 PM

You have the results listed as:

T
F
D
S
H
A

I think I can get the same results but in a different order:

T
S
H
F
D
A

Is that acceptable?

**FDibbins** · 05-31-2013, 04:56 PM

Hi and welcome to the forum

Please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you, and also, not all members can open that type of file

**roh710** · 05-31-2013, 08:10 PM

Well noted. I thought people may be reluctant to open an attached excel file. I'll attach a sample file next time.

Thanks for pointing that out.

KR

Originally Posted by FDibbins

Hi and welcome to the forum

Please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you, and also, not all members can open that type of file

**roh710** · 05-31-2013, 08:12 PM

Yes! a different order is perfectly acceptable. I can't wait to see your method.

Thanks, Biff!

KR

**Tony Valko** · 05-31-2013, 09:31 PM

Try this...

Data in the range A2:C4

Enter this array formula** in E2. This will return the count of unique entries.

=SUM(IF(A2:C4<>"",1/COUNTIF(A2:C4,A2:C4)))

Enter this array formula** in F2. This will extract the unique entries. There must be a cell above the first formula cell and this cell must not contain an item from the range.

=IF(ROWS(F$2:F2)>E$2,"",INDEX(A$2:C$4,MIN(IF(A$2:C$4<>"",IF(ISNA(MATCH(A$2:C$4,F$1:F1,0)),ROW(A$2:C$4)-ROW(A$2)+1))),MOD(MIN(IF(A$2:C$4<>"",IF(ISNA(MATCH(A$2:C$4,F$1:F1,0)),(ROW(A$2:C$4)-ROW(A$2)+1)*10^5+(COLUMN(A2:C4)-COLUMN(A2)+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.

Here's a small sample file that demonstrates this:

ExtractUniques2dRange.xlsx

**roh710** · 05-31-2013, 09:44 PM

Thank you Biff! This is amazing and it's exactly what I was looking for!

It will take some efforts in my part to follow through with the logic but I'm so glad you're able to solve this problem with just first try..

Thank you again Biff!

Originally Posted by Tony Valko

Try this...

Data in the range A2:C4

Enter this array formula** in E2. This will return the count of unique entries.

=SUM(IF(A2:C4<>"",1/COUNTIF(A2:C4,A2:C4)))

Enter this array formula** in F2. This will extract the unique entries. There must be a cell above the first formula cell and this cell must not contain an item from the range.

=IF(ROWS(F$2:F2)>E$2,"",INDEX(A$2:C$4,MIN(IF(A$2:C$4<>"",IF(ISNA(MATCH(A$2:C$4,F$1:F1,0)),ROW(A$2:C$4)-ROW(A$2)+1))),MOD(MIN(IF(A$2:C$4<>"",IF(ISNA(MATCH(A$2:C$4,F$1:F1,0)),(ROW(A$2:C$4)-ROW(A$2)+1)*10^5+(COLUMN(A2:C4)-COLUMN(A2)+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.

Here's a small sample file that demonstrates this:

ExtractUniques2dRange.xlsx

**Tony Valko** · 05-31-2013, 10:13 PM

You're welcome. Thanks for the feedback!

**roh710** · 05-31-2013, 10:57 PM

Hi Biff,

I have one follow-up question - If I want to change the range to include.. let's say, D3:AH50, what portion of the formula should I change? I replaced the range with a new range and I got an error.

Originally Posted by Tony Valko

You're welcome. Thanks for the feedback!

**roh710** · 05-31-2013, 11:07 PM

I solved it.. Thanks again for all your help!

Originally Posted by roh710

Hi Biff,

I have one follow-up question - If I want to change the range to include.. let's say, D3:AH50, what portion of the formula should I change? I replaced the range with a new range and I got an error.

**Tony Valko** · 06-01-2013, 10:59 PM

Good deal. Thanks for the feedback!

**nandkishorskale** · 09-09-2013, 04:40 PM

Hi Biff,

I have ona follow-up question -
If I want to change the range to include..
let's say, A2:H50, what portion of the formula should I change?
I replaced the range with a new range and I got an error.

Originally Posted by Tony Valko

Try this...

Data in the range A2:C4

Enter this array formula** in E2. This will return the count of unique entries.

=SUM(IF(A2:C4<>"",1/COUNTIF(A2:C4,A2:C4)))

Enter this array formula** in F2. This will extract the unique entries. There must be a cell above the first formula cell and this cell must not contain an item from the range.

=IF(ROWS(F$2:F2)>E$2,"",INDEX(A$2:C$4,MIN(IF(A$2:C$4<>"",IF(ISNA(MATCH(A$2:C$4,F$1:F1,0)),ROW(A$2:C$4)-ROW(A$2)+1))),MOD(MIN(IF(A$2:C$4<>"",IF(ISNA(MATCH(A$2:C$4,F$1:F1,0)),(ROW(A$2:C$4)-ROW(A$2)+1)*10^5+(COLUMN(A2:C4)-COLUMN(A2)+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.

Here's a small sample file that demonstrates this:

ExtractUniques2dRange.xlsx

**Tony Valko** · 09-09-2013, 06:28 PM

Please stay in your original thread.

It's against the forum rules to post ("hijack") in someone elses thread.

See my reply in your other thread.

**Glennstock** · 02-27-2017, 09:02 PM

Hello,
I have been reading other peoples solutions to the similar question I have, and have not been able to work out the answer.
Would someone please be able to offer an explanation to how I can solve this supposedly simple query.

I have a range of data that is duplicated numerous time, I wish to create a column that collates one of each entry with no duplicates.
Thanks very much for your help,
Glenn.

AC06001
AC06001
AC06001
AC06001
AH02001
AH02001
AH02001
AH02001
AH03006
AH03006
BG07001
BG07001
BG07002
BG08009
BR14001
CA22008
CA22008
CA22009
CA22009
CA22011
CA22011
CA22001
CA23010
CA24002
CA24002
CA24002
CA24002
CA24002
CA24002
CA24002
CA24002
CA24006
CA24006
CA24006
CA24006
CA24006
CA24006
CA24006
CA24006
CA24006
CA24006
CA24006
CA24006
CA24006
CA24006
CA24006
CA24001
CA11001
CA11001
CA11001
CA11001
CA11001
CA11001
CA28002
CA13001
CT14001
CT14001
LW11002
MA04009
MA04009
MA04012
MA04012
MA04012
MA04015
MA04011
MA04007
MA04018
MA04018
NB03002
PE08003
PE08003
PE08006
PE08007
PE08013
PE08013
PE08019
PE08019
RD24001
RD24001
RD24001
RD26001
RD26001
RD26001
RD26002
RD26002
RD26002
RD26002
CT12003
SR09001
SR09001
SR09001
SR09001
SR09001
SR09001
SR09001
SR09002
SR09002
SR09002
SR09002
SR09003
SR09003
SR09003
SR09003
SR09003
SR09003
SR09003
SR09003
SR09003
SR09003
SR09003
SR09003
SR09005
SR16002
SR16002
SR14005
SR14005
SR14005
SR14005
SV01007
SV01008
SV01008
WO08002
WS01005
BG11007
CA12001
CA12001
DB09001
DB14002
DB14008
DB14007
KL08002
MF07001
NL01001
NL05001
NL05001
NL05001
NL05001
NL05001
NL05001
NL05001
NL05001
NL05001
NL05001
SR01002
SR01003
SR01003
SR01003
RD27004
RD27004

**FlameRetired** · 02-27-2017, 09:12 PM

Glennstock welcome to the forum.

As a new member you may not be aware that what you are doing is called 'hijacking' someone else's thread. It's against forum rules.

Please take the time to read those rules.

Then start your own thread and post your question there.

Thanks,
Dave

Listing multiple column entries into a single column

LinkBack

Thread Tools

Rate This Thread

Display

Listing multiple column entries into a single column

Re: Listing multiple column entries into a single column

Re: Listing multiple column entries into a single column

Re: Listing multiple column entries into a single column

Re: Listing multiple column entries into a single column

Re: Listing multiple column entries into a single column

Re: Listing multiple column entries into a single column

Re: Listing multiple column entries into a single column

Re: Listing multiple column entries into a single column

Re: Listing multiple column entries into a single column

Re: Listing multiple column entries into a single column

Re: Listing multiple column entries into a single column

Re: Listing multiple column entries into a single column

Re: Listing multiple column entries into a single column

Re: Listing multiple column entries into a single column

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions