Filter for Unique Values in Individual Columns Across Large Dataset

[VCUOA]

Hello folks

I have a problem that cannot be completed using the advanced filter; I have >1500 columns in a dataset (D:BIH), and I need to extract the unique values for each individual column. The advanced filter fails after doing any more than 3 columns at a time, simply doing a copy/paste of all of the column's contents to the new location as opposed to a list of the unique values. I have several datasets all containing >1500 columns that I need to work through, and doing each column individually would be less than ideal!

My aim is to extract a list of unique values per column across the dataset, not a list of unique values for the entire array. I will then use the 'COUNTIF()' function to count the numbers of each unique value in the column, for all individual columns. My array is D1:BIH37.

Any and all help is greatly appreciated. Thanks.

 

[shg]

Does (would) Remove Duplicates on each column individually generate the results you want?

 

[hiker95]

VCUOA,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


3. What is the name of the worksheet containing the raw data?

4. What is the name of the worksheet that will display the results?


5. Can we have a screenshot of the raw data worksheet, range D1:F37?

6. Can we have a screenshot (manually formatted by you) for the results you are looking for, in the resulting worksheet, and, including the COUNTIF formulae?


To post your data, you can download and install one of the following two programs:
1. MrExcel HTMLMaker20101230
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Installation instructions here:
http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html#post2545970

2. Excel Jeanie
Download


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[VCUOA]

To Shg

It could, if I could do it en mass and copy it back to another sheet. Is it possible to remove duplicates from individual columns (x1500) in on go? If I had to manually do them all individually then this does not solve my problem. Thanks for the rapid reply!

 

[hiker95]

VCUOA,

I am thinking of a macro solution.

But, I will need you to answer the 6 questions in my reply #3, and, we will need to see your raw data, and, what the results should look like (per my reply #3 instructions).

 

[VCUOA]

Hello hiker95

Thank you very much for your response. It will take me a moment to edit my file into an example; I cannot provide the authentic data due to confidentiality reasons, however I provide an edited sample that you can use. I will post very soon.

 

[shg]

Here's what I meant:

Sub VCUOA()
    Dim col As Range

    
    For Each col In ActiveSheet.UsedRange.Columns
        col.RemoveDuplicates Columns:=1, Header:=xlYes
    Next col
End Sub

That deletes duplicates in situ. It could be modified to make a copy on a fresh sheet first.

 

[VCUOA]

hiker95,
1) Excel 2007, Windows 7 Home Premium SP1.
2) PC
3)S1.1
4)Preferably on the same sheet, below the column (outside of the array, can be seen on second screenshot) however an output onto S1.2 would be fine. I am happy to recompile data.
5)

6)

 

[VCUOA]

Shg,
That script worked a treat, thank you. It takes a bit of copy and paste but it really does help. Would you happen to know of a script that would erase all individual cells with a '0' value, and shift the remaining cells up to fill the gap? It would make my worksheet splicing much quicker.

Thank you for your help, you guys are fantastic examples of excellent forum users.

 

[hiker95]

VCUOA,

You are posting pictures/graphics. This means that if this was a problem where one needed to use your data, anyone trying to help you would have to enter the data manually. That makes no sense.