VBA Solution: Concatenate Range, Move Subsequently Created Values Into Single Column

[SteveOranjinSteve]

Hello,

Hope you are well. I am having difficulty composing a formula for a problem I'm having, and I may have to work through this issue again. What I'd like to do, is to concatenate a range of cells, so that I can create unique values (for a SKU-Country Code Combination) and then put all of those values into a single column. Here is how the data is set up.

	A	B	C	D	E	F	G
1	134555	AE	AE	AE	ae	BN	BN
2	232545	EA	AE	Ee	ae	BN	BN
3	124142	AW	AE	ea	ZN	ZN	BX
4	124142	AE	AE	ae	BC	CB	BC

a couple of points to be aware of.

1. The data has NO HEADERS
2. Horizontally, there is no general rule as to how far out the data goes. Sometimes it goes out 2 columns, sometimes 250 or 300 lines.
3. Without exception, the country codes are two letters in length.
4. Sometimes the SKUs are set up as depicted, sometimes they have "dashes" in them as well. I'd err on the side of caution and allow for letters as well.

The data in its final form should look something like this. Please let me know if you have any other questions.

134555AE

134555BN

232545AE

232545EE

232545BN

The results can be on the same tab, or on a new tab. it does not matter.

 

[Fluff]

How about

Sub SteveOranjin()
   Dim ary As Variant
   Dim r As Long, c As Long
   
   ary = Sheets("Sheet1").Range("A1").CurrentRegion.Value2
   With CreateObject("scripting.dictionary")
      .CompareMode = 1
      For r = 1 To UBound(ary)
         For c = 2 To UBound(ary, 2)
            If ary(r, c) = "" Then Exit For
            .Item(ary(r, 1) & UCase(ary(r, c))) = Empty
         Next c
      Next r
      Sheets("Sheet2").Range("A1").Resize(.Count).Value = Application.Transpose(.Keys)
   End With
End Sub

change sheet names to suit

 

[SteveOranjinSteve]

Thanks

 

[Fluff]

You're welcome