extract or copy only one value that's unique across multiple columns

[monere]

Hey, guys!

I have a 10-column sheet with both unique and non-unique values (which are actually text, not numeric values, if this matters) across different columns. For example... column 1 has 27 rows of text, column 2 has 33 rows of text, column 3 has 14 rows of text, column 4 has 62 rows of text, and so on.... so random number of values but also random values but which sometimes coincide with the values in other columns, and sometimes they don't.

Now, these values are 1-word, 2-words, or 3-words, in case that this matters in your helping me solve my issue.

So, what I'm trying to do is either extract all of the unique values across the entire sheet and insert them into a separate column, or copy those unique values to clipboard so I can paste them into a separate column myself. But the trick is that it only one instance of each value needs to be extracted / copied, but all of the values that appear at least once across all columns need to be extracted / copied, as well.

For example, let's say that a value appears in columns 2, 5, and 9. So, it appears 3 times in total across the entire sheet. Well, I would like excel to - like I said - either extract this value out of whichever column (I don't care where it extracts it from, I only need the value itself, I don't care if the configuration of the sheet gets screwed) and automatically paste it into a designated column (which I can manually create, or let the software do it, I don't care again), or just copy it to the clipboard so I can manually paste it into whichever column I choose.

And after this is done with the 1st value that excel reads starting with R1C1, it will then move onto the value that's in R2C1, then R3C1, and so on...

I forgot to mention that each column contains only 1 instance of whichever value is in that column, so there won't be any duplicate values within any 1 column itself, but there will be duplicate values across multiple columns, like I said.

So yeah, that's what I'm trying to do, I hope that you, smart folks, could understand what I'm trying to accomplish and can help me out, because I'm not good with such advanced methods of data manipulation despite of the fact that I do like excel and toy with it as much as I get the chance.

So, can someone give me a tip or 2 on how to do this? I could do it manually without a problem as there are not many values to sift through and extract individually, but the problem is that this is only 1 file,, and soon I'll have to do this exact same thing for more files and it's just not time-efficient doing this manually, especially with the other files containing even more data. I think that finding a way to automate this is the best way to go about it, but alas, I'm not tech-savvy so I always struggle figuring tech stuff on my own.

Anyway, please let me know how to accomplish this if it's not too much trouble. Thanks in advance

 

[shinigamilight]

Sub twoo()
            Dim k, i As Integer
            Dim lr, lc As Long
            Dim wk1, wk2 As Worksheet
            Set wk1 = Sheets("testing") ' add your input sheetname within the double quotes
            Set wk2 = Sheets("outputer") 'add your output sheetname within the double quotes
           ' make sure the output sheet is blank
           
            lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlWhole, xlByRows, xlPrevious, False).Row
            lc = Cells(1, Columns.Count).End(xlToLeft).Column
            wk2.Cells(1, 1).Value = "Data"
            For i = 2 To lc
                    For k = 1 To lr
                            If Cells(k, i) <> "" Then
                                Cells(k, i).Copy wk2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
                            End If
                    Next k
            Next i

            With wk2.Range("A1:A" & wk2.Cells(1, 1).End(xlDown).Row)
                    .AdvancedFilter xlFilterCopy, , wk2.Range("B1"), 1
            End With
           
            wk2.Columns(1).Delete
            wk2.UsedRange.EntireColumn.AutoFit

End Sub

 

[monere]

Sub twoo()
            Dim k, i As Integer
            Dim lr, lc As Long
            Dim wk1, wk2 As Worksheet
            Set wk1 = Sheets("testing") ' add your input sheetname within the double quotes
            Set wk2 = Sheets("outputer") 'add your output sheetname within the double quotes
           ' make sure the output sheet is blank
          
            lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlWhole, xlByRows, xlPrevious, False).Row
            lc = Cells(1, Columns.Count).End(xlToLeft).Column
            wk2.Cells(1, 1).Value = "Data"
            For i = 2 To lc
                    For k = 1 To lr
                            If Cells(k, i) <> "" Then
                                Cells(k, i).Copy wk2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
                            End If
                    Next k
            Next i

            With wk2.Range("A1:A" & wk2.Cells(1, 1).End(xlDown).Row)
                    .AdvancedFilter xlFilterCopy, , wk2.Range("B1"), 1
            End With
          
            wk2.Columns(1).Delete
            wk2.UsedRange.EntireColumn.AutoFit

End Sub

Hi,

how do I use this?

 

[shinigamilight]

Hi,

how do I use this?

it says right there in green text what u need to do. Paste this in vba editor.

 

[Peter_SSs]

when I use it in the real file it returns numbers 1 to 5 for the first 5 rows, but then it works as intended. What could be the problem with those first 5 rows?

The most likely cause would be that the numbers 1 to 5 occupy the first row of the range that the formula is pointed at.

monere.xlsm
	A	B	C	D	E	F
1	Unique List	1	2	3	4	5
2	1	Australian Shepherd	Australian Shepherd	Australian Shepherd	Cardigan Welsh Corgi	American Water Spaniel
3	2	Basset Hound	Airedale Terrier	American Water Spaniel	Chihuahua	Beagle
4	3	Black Russian Terrier	American Water Spaniel	Beagle	Chinese Crested	Basset Hound
5	4	Cardigan Welsh Corgi	Beagle	Basset Hound	Dachshund	Chihuahua
6	5	Chihuahua	Basset Hound	Black Russian Terrier	Doberman Pinscher	Chinese Crested
7	Australian Shepherd	Chinese Crested	Black Russian Terrier	Cardigan Welsh Corgi	French Bulldog	Dachshund
8	Cardigan Welsh Corgi	Dachshund	Cardigan Welsh Corgi	Chinese Crested	Flat-Coated Retriever	Doberman Pinscher
9	American Water Spaniel	Karelian Bear Dog	Chihuahua	Dachshund	Golden Retriever	English Setter
10	Basset Hound	Leonberger	Chinese Crested	Doberman Pinscher	Greyhound	Eurasier
Sheet1

Cell Formulas
Range	Formula
A2:A10	A2	=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,(ROW(B$1:F$41)*10^6+COLUMN(B$1:F$41))/((B$1:F$41<>"")*(ISNA(MATCH(B$1:F$41,A$1:A1,0)))),1),"R000000C000000"),0),"")

 

[monere]

it says right there in green text what u need to do. Paste this in vba editor.

I saw that, but I don't know what to do with the entire code itself., so the instructions in green are the least of my concern

But hold on please, I might use a formula instead of VBA... If the formula won't work I'll use your method for sure..

The most likely cause would be that the numbers 1 to 5 occupy the first row of the range that the formula is pointed at.

Yes, the column headers are indeed numbers

I used the new formula but it still outputs the headers instead of data. Also, would it be possible to make the formula cover 95 rows and 13 columns of data instead of the 40x5 that I used as sample? I think that 95x13 will be the biggest data set that I'll be working with for this project.

 

[Peter_SSs]

I used the new formula but it still outputs the headers instead of data.

Only if you point it at the headers.

Also, would it be possible to make the formula cover 95 rows and 13 columns of data instead of the 40x5 that I used as sample?

Sure, just point it at the relevant range

=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,(ROW(B$1:F$40)*10^6+COLUMN(B$1:F$40))/((B$1:F$40<>"")*(ISNA(MATCH(B$1:F$40,A$1:A1,0)))),1),"R000000C000000"),0),"")

 

[monere]

Only if you point it at the headers.


Sure, just point it at the relevant range

=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,(ROW(B$1:F$40)*10^6+COLUMN(B$1:F$40))/((B$1:F$40<>"")*(ISNA(MATCH(B$1:F$40,A$1:A1,0)))),1),"R000000C000000"),0),"")

It worked! It freakin' WORKED

Thanks, dude! You're a wizard

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[monere]

I'm the one who should say thanks because you saved me a lot of grinding

Cheers!