Copy Columns A to F into H, ignoring any duplicates or blanks.

Hi folks,

I'm looking for some help transferring lists from several columns into 1.

I have data in A2:A8, B2:17, C2:C8, D2:D19, E2:E13 & F2:F7.

What I want to do is take all of that data, and copy it across to column H ignoring any blanks or duplicates in the data, to create one singular list of data.

I feel as though I've been close several times with an IF(COUNTIF...) attempt, without nailing it, and feeling as though the answer is staring me in the face. Any help would be greatly appreciated!

Originally Posted by AliGW

See the yellow banner at the top of the page.

Hi Ali,

Thanks for the reply. Are you referring to creating a small example and attaching it? Just checking, as I have 3 yellow banners at the top of my page.

I've created a file with the information detailed above and attached. If anyone can help, it's much appreciated!

I'm still struggling with this, so if anyone can help I'd really appreciate it!

Thanks for the reply Ali. Not sure how else I can explain it differently from my 1st post, but I'll give it a shot.

Columns A to F will have various entries. Some will be duplicates of each other. Some will just be blank spaces.

In Column H, I would like to list all the entries from the first 6 columns but removing duplicates and blanks.

Ideally so it would look something like my attached file, but I don't know how to do the formula for Column G that would remove the duplicates and blanks from the data. (It doesn't matter what order they are in for the finalised list).

Thank you again for all the help with this, I can't stress how thankful I am for trying to sort this!

Thanks for the help Glenn, I'm just testing out now to see if that works.

As for the 3 banners, 2 look like they were for when you are new to the forum, and can be removed by clicking the "x" at the end of them (which I've now down unfortunately, so I can't screenshot).