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



## monere (Dec 14, 2022)

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


----------



## monere (Dec 15, 2022)

Can anybody chime in? Pretty please?


----------



## Peter_SSs (Dec 15, 2022)

monere said:


> I hope that you, smart folks, could understand what I'm trying to accomplish ..


Not easy from a long wordy description. What about a small set of dummy sample data *and *the expected results?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.


----------



## monere (Dec 16, 2022)

Peter_SSs said:


> Not easy from a long wordy description.


I don't like long descriptions either, but I can assure you that I only wrote what needed to be mentioned to explain the issue. I would never bore you, guys, with useless ramblings, don't worry 



Peter_SSs said:


> What about a small set of dummy sample data *and *the expected results?


unfortunately, I can't provide actual data as it's private, but I'll figure out something when I get home (in about 2-3 hours, possibly earlier but we will see...)

The expected results is what I just mentioned in my OP, ie: all values that are unique across the entire sheet need to be extracted (cut-pasted), or copied to clipboard and pasted into a separate column (whichever method works, I'm not fussy about it)



Peter_SSs said:


> MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in


got it! I'll take a look at it when I get home...

Cheers!


----------



## Peter_SSs (Dec 16, 2022)

monere said:


> unfortunately, I can't provide *actual* data as it's private,


That is why is said


Peter_SSs said:


> What about a small set of *dummy *sample data *and *the expected results?


----------



## monere (Dec 16, 2022)

Peter_SSs said:


> That is why is said


Fair point, but somehow I missed it. I apologize

Here's the mini sheet thing, I hope that I did it right. If not, I'll gladly fix what needs to be fixed. Also, the dummy data that I've used is dog breeds, I hope that it's ok as dummy text as it's pretty close to what my real data is anyway

Based on your instructions, I've posted the table below to show how my real sheet would look like, but there are still a few things that need to be mentioned to get the full picture of what I'm trying to do:

1) the table below contains 5 columns of data (there are 6 columns actually, but column A shows what the end result - and the reason for which I've opened up this thread - needs to be, while columns B to F are the data that column A needs to extract the data from), but my real sheet would have more columns of data. So, if there's a formula that needs to include the number of columns in the final solution please let me know so that I can adjust that formula according to my case;

2) highlighted in yellow are 6 dog breeds that appear only once in the entire sheet. I've purposely added these 6 breeds in, because my real sheet would also include once-off values spread randomly throughout the entire sheet, and this is important because these once-off occurrences also need to be extracted / copied into column A, not just the values that repeat in multiple columns;

3) the censored word in R13C1 is C.0.C.K.E.R, sorry for the censoring, but that's probably been done by your software as my excel doesn't censor words;

4) while in the table below there are no such entries, in the real sheet there will be 2-word or 3-word entries that will contain special characters (most of the time hyphens, underscores or slashes, but there could also be "greater than" signs or brackets) and those special characters / symbols need to be carried into the final solution, so this is probably important to mention.

And that's it! I don't think such data is hard to manipulate by a tool like excel, but I and science are not good friends, so I have no idea how to do it myself 

xl2bb.xlsxABCDEF1Australian ShepherdAustralian ShepherdAustralian ShepherdAustralian ShepherdCardigan Welsh CorgiAmerican Water Spaniel2Airedale TerrierBasset HoundAiredale TerrierAmerican Water SpanielChihuahuaBeagle3American Water SpanielBlack Russian TerrierAmerican Water SpanielBeagleChinese CrestedBasset Hound4BeagleCardigan Welsh CorgiBeagleBasset HoundDachshundChihuahua5Basset HoundChihuahuaBasset HoundBlack Russian TerrierDoberman PinscherChinese Crested6Black Russian TerrierChinese CrestedBlack Russian TerrierCardigan Welsh CorgiFrench BulldogDachshund7Cardigan Welsh CorgiDachshundCardigan Welsh CorgiChinese CrestedFlat-Coated RetrieverDoberman Pinscher8ChihuahuaKarelian Bear DogChihuahuaDachshundGolden RetrieverEnglish Setter9Chinese CrestedLeonbergerChinese CrestedDoberman PinscherGreyhoundEurasier10DachshundLucas TerrierDachshundEnglish ****er SpanielGerman Longhaired PointerFrench Bulldog11Doberman PinscherMastiffDoberman PinscherEnglish SetterHavaneseFlat-Coated Retriever12Danish-Swedish FarmdogMountain FeistDanish-Swedish FarmdogEurasierHamilton HoundHamilton Hound13English ****er SpanielOld English SheepdogEnglish ****er SpanielFlat-Coated RetrieverIrish Water SpanielIrish Setter14English SetterPoodleEnglish SetterGolden RetrieverJack Russell TerrierIcelandic Sheepdog15EurasierPortuguese Podengo PequenoEurasierGreyhoundJagdterrierJack Russell Terrier16French BulldogPicardy SpanielFrench BulldogHavaneseKeeshondJagdterrier17Flat-Coated RetrieverRhodesian RidgebackFlat-Coated RetrieverHamilton HoundKarelian Bear DogKeeshond18Golden RetrieverRottweilerGolden RetrieverIrish SetterLeonbergerKorean Jindo19GreyhoundWhippetGreyhoundIcelandic SheepdogPoodleLeonberger20German Longhaired PointerGerman Longhaired PointerJack Russell TerrierPortuguese Podengo PequenoMountain Feist21HavaneseHavaneseJagdterrierPicardy SpanielOld English Sheepdog22Hamilton HoundHamilton HoundKeeshondYorkshire TerrierPoodle23Irish SetterIrish SetterKorean JindoPortuguese Podengo Pequeno24Icelandic SheepdogIcelandic SheepdogKarelian Bear DogPicardy Spaniel25Irish Water SpanielIrish Water SpanielLeonbergerSiberian Husky26Jack Russell TerrierJack Russell TerrierMastiffSloughi27JagdterrierJagdterrierMountain FeistToy Fox Terrier28KeeshondKeeshondOld English Sheepdog29Korean JindoKorean JindoPortuguese Podengo Pequeno30Karelian Bear DogKarelian Bear DogPicardy Spaniel31LeonbergerLeonbergerRhodesian Ridgeback32Lucas TerrierLucas TerrierRottweiler33MastiffMastiffVizsla34Mountain FeistMountain Feist35Old English SheepdogOld English Sheepdog36PoodlePoodle37Portuguese Podengo PequenoPortuguese Podengo Pequeno38Picardy SpanielPicardy Spaniel39Rhodesian RidgebackRhodesian Ridgeback40RottweilerRottweiler41Siberian Husky42Sloughi43Toy Fox Terrier44Vizsla45Whippet46Yorkshire TerrierSheet1


----------



## Peter_SSs (Dec 16, 2022)

Sorry if I am missing something, but I can't see that the yellow values are anything different to the others.
So, doesn't this just boil down to simply "produce a list of the unique values that appear in the multi-column data"?
If so, and you are looking for a formula solution (rather than vba or power query), that will work with Excel 2016, what about this?

monere.xlsmABCDEF1Unique ListAustralian ShepherdAustralian ShepherdAustralian ShepherdCardigan Welsh CorgiAmerican Water Spaniel2Australian ShepherdBasset HoundAiredale TerrierAmerican Water SpanielChihuahuaBeagle3Cardigan Welsh CorgiBlack Russian TerrierAmerican Water SpanielBeagleChinese CrestedBasset Hound4American Water SpanielCardigan Welsh CorgiBeagleBasset HoundDachshundChihuahua5Basset HoundChihuahuaBasset HoundBlack Russian TerrierDoberman PinscherChinese Crested6Airedale TerrierChinese CrestedBlack Russian TerrierCardigan Welsh CorgiFrench BulldogDachshund7ChihuahuaDachshundCardigan Welsh CorgiChinese CrestedFlat-Coated RetrieverDoberman Pinscher8BeagleKarelian Bear DogChihuahuaDachshundGolden RetrieverEnglish Setter9Black Russian TerrierLeonbergerChinese CrestedDoberman PinscherGreyhoundEurasier10Chinese CrestedLucas TerrierDachshundEnglish ****er SpanielGerman Longhaired PointerFrench Bulldog11DachshundMastiffDoberman PinscherEnglish SetterHavaneseFlat-Coated Retriever12Doberman PinscherMountain FeistDanish-Swedish FarmdogEurasierHamilton HoundHamilton Hound13French BulldogOld English SheepdogEnglish ****er SpanielFlat-Coated RetrieverIrish Water SpanielIrish Setter14Flat-Coated RetrieverPoodleEnglish SetterGolden RetrieverJack Russell TerrierIcelandic Sheepdog15Karelian Bear DogPortuguese Podengo PequenoEurasierGreyhoundJagdterrierJack Russell Terrier16Golden RetrieverPicardy SpanielFrench BulldogHavaneseKeeshondJagdterrier17English SetterRhodesian RidgebackFlat-Coated RetrieverHamilton HoundKarelian Bear DogKeeshond18LeonbergerRottweilerGolden RetrieverIrish SetterLeonbergerKorean Jindo19GreyhoundWhippetGreyhoundIcelandic SheepdogPoodleLeonberger20EurasierGerman Longhaired PointerJack Russell TerrierPortuguese Podengo PequenoMountain Feist21Lucas TerrierHavaneseJagdterrierPicardy SpanielOld English Sheepdog22English ****er SpanielHamilton HoundKeeshondYorkshire TerrierPoodle23German Longhaired PointerIrish SetterKorean JindoPortuguese Podengo Pequeno24MastiffIcelandic SheepdogKarelian Bear DogPicardy Spaniel25HavaneseIrish Water SpanielLeonbergerSiberian Husky26Mountain FeistJack Russell TerrierMastiffSloughi27Danish-Swedish FarmdogJagdterrierMountain FeistToy Fox Terrier28Hamilton HoundKeeshondOld English Sheepdog29Old English SheepdogKorean JindoPortuguese Podengo Pequeno30Irish Water SpanielKarelian Bear DogPicardy Spaniel31Irish SetterLeonbergerRhodesian Ridgeback32PoodleLucas TerrierRottweiler33Jack Russell TerrierMastiffVizsla34Icelandic SheepdogMountain Feist35Portuguese Podengo PequenoOld English Sheepdog36JagdterrierPoodle37Picardy SpanielPortuguese Podengo Pequeno38KeeshondPicardy Spaniel39Rhodesian RidgebackRhodesian Ridgeback40RottweilerRottweiler41Korean Jindo42Whippet43Yorkshire Terrier44Siberian Husky45Sloughi46Toy Fox Terrier47Vizsla48 Sheet1Cell FormulasRangeFormulaA2:A48A2=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 (Dec 16, 2022)

Peter_SSs said:


> So, doesn't this just boil down to simply "produce a list of the unique values that appear in the multi-column data"?


I guess...



Peter_SSs said:


> If so, and you are looking for a formula solution (rather than vba or power query), that will work with Excel 2016, what about this?


I don't know what vba and power query is, but I'm not fussy about which method I use as long as I'm capable of using it :D

Regarding the formula you shared, I'm getting zeros everywhere except for the 2nd row, as you can see in the screenshots below.

Also, upon copy-pasting the formula into cell A1 and hitting ENTER I get this error message:






Here's what it looks like with the formula inserted into several consecutive cells:


----------



## Peter_SSs (Dec 16, 2022)

If you look more closely at my sample, the formula starts in cell A2, not A1. It needs to do that.


----------



## monere (Dec 16, 2022)

oops, my bad 

Yeah, the formula works on the sample file, but 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?


----------



## monere (Dec 14, 2022)

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 (Dec 16, 2022)

```
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 (Dec 16, 2022)

shinigamilight said:


> ```
> Sub twoo()
> Dim k, i As Integer
> Dim lr, lc As Long
> ...


Hi,

how do I use this?


----------



## shinigamilight (Dec 16, 2022)

monere said:


> 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 (Dec 16, 2022)

monere said:


> 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.xlsmABCDEF1Unique List1234521Australian ShepherdAustralian ShepherdAustralian ShepherdCardigan Welsh CorgiAmerican Water Spaniel32Basset HoundAiredale TerrierAmerican Water SpanielChihuahuaBeagle43Black Russian TerrierAmerican Water SpanielBeagleChinese CrestedBasset Hound54Cardigan Welsh CorgiBeagleBasset HoundDachshundChihuahua65ChihuahuaBasset HoundBlack Russian TerrierDoberman PinscherChinese Crested7Australian ShepherdChinese CrestedBlack Russian TerrierCardigan Welsh CorgiFrench BulldogDachshund8Cardigan Welsh CorgiDachshundCardigan Welsh CorgiChinese CrestedFlat-Coated RetrieverDoberman Pinscher9American Water SpanielKarelian Bear DogChihuahuaDachshundGolden RetrieverEnglish Setter10Basset HoundLeonbergerChinese CrestedDoberman PinscherGreyhoundEurasierSheet1Cell FormulasRangeFormulaA2: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),"")


----------



## monere (Dec 17, 2022)

shinigamilight said:


> 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..



Peter_SSs said:


> 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 (Dec 17, 2022)

monere said:


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


Only if you point it at the headers.



monere said:


> 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 (Dec 17, 2022)

Peter_SSs said:


> Only if you point it at the headers.
> 
> 
> Sure, just* point it at the relevant range*
> ...


It worked! It freakin' WORKED   

Thanks, dude! You're a wizard


----------



## Peter_SSs (Dec 17, 2022)

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


----------



## monere (Dec 18, 2022)

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

Cheers!


----------

