Formula to tidy a lists

[Nitro2481]

Hi Guys,

I'm looking to create a tidy version of a list I have. Example.

Column A1 down Customer name
Mickey Mouse
Donald Duck
Minnie Mounse
Daisy Duck
Etc

Column B1 down Product bought
Apple
Apple
Banana
Banana
Apple
Grapes
etc


I want column C to create a list of all products sold, but without any gaps or duplicates
So Column C1 down giving the example above would read
Apple
Banana
Grapes

Hope this makes sense & thanks in advance

[FioreMatto]

Buongiorno
poniamo i dati da B2 a scendere in C2 da trascinare in basso

=SE.ERRORE(INDICE(B$2:B$100;CONFRONTA(0;INDICE(CONTA.SE($C$1:C1;$B$2:B$100&"");0));"")

[Richard Buttrey]

If you only have Excel 2003 and not (I think 2007 and later) then you could use the Advanced Data Filter to extract a unique list.

Otherwise just select the range i question and use the Remove Duplicates option on the Ribbon

[Nitro2481]

I have tried that in my sheet and I get #Name?. I'm using 2003

Please Login or Register  to view this content.

[Nitro2481]

Thanks Richard,

Was hoping for something a little more Automatic. I will be using this tidies list in a formula else where so would like it constantly updated.

[Richard Buttrey]

Thanks Richard,

Was hoping for something a little more Automatic. I will be using this tidies list in a formula else where so would like it constantly updated.

Don't forget that for most things you do manually you can easily automate them with a macro.

Use the macro recorder to do the task then upload the workbook so that we can generalise the macro you captured.

[Nitro2481]

Hi Richard,

Really appreciate you taking the time

The way I have it is

eg

I have a "product" list in a number of different columns. Eg B, C, D,E, F, G, H, I etc
So in J I am looking for the tidied list of B, in K the tidied list of C and in L the tidied list of D and so on. I have 8 lists in total

I dont think Autofilter will do that?

That guys formula above seemed to work when I opened the sample spreadsheed he supplied but when I press enter it changed to #Name? Is this because I'm 2003. Is there no formula I can use for 2003?

[63falcondude]

IFERROR does not work in Excel 2003. You can use IF and ISERROR to achieve the same result.

Based on the sample that you have shared in post #1, try this in C2:

=IF(ISERROR(INDEX(B$2:B$100,MATCH(0,COUNTIF(C$1:C1,B$2:B$100),0))),"",INDEX(B$2:B$100,MATCH(0,COUNTIF(C$1:C1,B$2:B$100),0))) Ctrl Shift Enter

Drag the formula down. It is currently set up to handle 99 rows of data (rows 2:100).

[Richard Buttrey]

Hi,

I didn't actually mention the Autofilter, I suggested either the Data Advanced Filter or the RemoveDuplicates syntax

Here's the macro, the file is attached

Please Login or Register  to view this content.

[Nitro2481]

Think I'm nearly there. This is still showing Duplicates. My row Starts on 3 so I'm assuming I change the "B's" to 3. Should I change the C1's too?

[63falcondude]

In your original post, you said that your data starts with headers in row 1 so that's what the formula in post #8 was based on.

If your data starts in row 3, you can use this in C3:

=IF(ISERROR(INDEX(B$3:B$100,MATCH(0,COUNTIF(C$2:C2,B$3:B$100),0))),"",INDEX(B$3:B$100,MATCH(0,COUNTIF(C$2:C2,B$3:B$100),0))) Ctrl Shift Enter

[Nitro2481]

Absolute Gent.

Worked perfect. Thanks to everyone for taking the time.

[63falcondude]

Happy to help. Thanks for the rep!