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

monere

Board Regular
Joined
Jul 12, 2014
Messages
149
Office Version
  1. 2016
Platform
  1. Windows
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 :)
 
VBA Code:
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
 

Attachments

  • 1671195732356.png
    1671195732356.png
    22.5 KB · Views: 17
Upvote 0

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
VBA Code:
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?
 
Upvote 0
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
ABCDEF
1Unique List12345
21Australian ShepherdAustralian ShepherdAustralian ShepherdCardigan Welsh CorgiAmerican Water Spaniel
32Basset HoundAiredale TerrierAmerican Water SpanielChihuahuaBeagle
43Black Russian TerrierAmerican Water SpanielBeagleChinese CrestedBasset Hound
54Cardigan Welsh CorgiBeagleBasset HoundDachshundChihuahua
65ChihuahuaBasset HoundBlack Russian TerrierDoberman PinscherChinese Crested
7Australian ShepherdChinese CrestedBlack Russian TerrierCardigan Welsh CorgiFrench BulldogDachshund
8Cardigan Welsh CorgiDachshundCardigan Welsh CorgiChinese CrestedFlat-Coated RetrieverDoberman Pinscher
9American Water SpanielKarelian Bear DogChihuahuaDachshundGolden RetrieverEnglish Setter
10Basset HoundLeonbergerChinese CrestedDoberman PinscherGreyhoundEurasier
Sheet1
Cell Formulas
RangeFormula
A2:A10A2=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),"")
 
Upvote 0
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 :biggrin:

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.
 
Upvote 0
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),"")
 
Upvote 0
Solution
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 :biggrin:

Thanks, dude! You're a wizard (y)
 
Upvote 0
I'm the one who should say thanks because you saved me a lot of grinding :)

Cheers!
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top