Transposing a Column into A Row Alphabetically Based on Criterion

[The_T]

Hello community,

I have a challenge that I am currently unable to overcome:

In one worksheet, I have two columns; one entitled 'TYPE' and one entitled 'PRODUCT CODE'.

Under TYPE, you will either have the word 'ALPHA', 'BETA' or 'OMEGA'.

Under PRODUCT CODE, you could have one of a huge variety of reference codes.

For Example:

TYPE PRODUCT CODE
Alpha 001-234
Alpha SA5216
Omega KLM533
Alpha ZZ22345
Beta 002-333
Beta LLL-Z22

In another worksheet, I need to transpose the PRODUCT CODE column into a row.

Additionally, I only want product codes to appear in the row of transposed data that have the word 'Alpha' under the TYPE column. So for the above example, the transposed row in the second worksheet would look like this:

SA5216 ZZ22345 001-234

One further issue; the worksheet where the original data is found can be filtered by users (so the data could move around, e.g. sort by ascending and descending). However, the worksheet where the PRODCUT CODE column is to be transposed into one row HAS to have this data fixed in alphabetical order (so that when the first worksheet is being filtered, the order of the data in the transposed row found in the second sheet remains fixed).

I have played around by mixing IF and TRANSPOSE functions but I imagine the above is only/best achieved via a macro and I can't write this.

Could somebody pelase advise. You'd be doing me a masssive favour.

Cheers.

T

 

[DanteAmor]

According to the order of excel, first the numbers go and then the letters, then the result would be:

001-234 SA5216 ZZ22345

Try this code, change the letters in blue for the name of your sheets.

Sub Transposing_Column_To_Row()
  Dim arrList As Object, a As Variant, i As Long
  Set arrList = CreateObject("System.Collections.ArrayList")
  a = Sheets("[COLOR=#0000ff]Sheet1[/COLOR]").Range("A2:B" & Sheets("[COLOR=#0000ff]Sheet1[/COLOR]").Range("A" & Rows.Count).End(xlUp).Row)
  For i = 1 To UBound(a)
    If a(i, 1) = "[COLOR=#008000]Alpha[/COLOR]" Then arrList.Add CStr(a(i, 2))
  Next
  arrList.Sort
  Sheets("[COLOR=#0000ff]Sheet2[/COLOR]").Range("A2").Resize(1, arrList.Count).Value = arrList.toArray
End Sub

 

[The_T]

Hi DanteAmor.

This works great, thanks.

Just wondering if there is a way for the macro to add a column if someone added to the original list in the first sheet.

Basically, if somebody adds a TYPE Alpha to the end of the the list in the first worksheet with a PRODUCT CODE that isn't last alphabetically, can the macro be modified to insert a column in the right place in the order in the second worksheet to then include it.

Reason being, users will have to add data below the transposed row in the second worksheet and if the transposed row simply includes the new PRODUCT TYPE without inserting a new column, all the date in the column below the new entry will be for another product code.

Any help would be greatly appreciated.

 

[The_T]

According to the order of excel, first the numbers go and then the letters, then the result would be:

001-234 SA5216 ZZ22345

Try this code, change the letters in blue for the name of your sheets.

Sub Transposing_Column_To_Row()
  Dim arrList As Object, a As Variant, i As Long
  Set arrList = CreateObject("System.Collections.ArrayList")
  a = Sheets("[COLOR=#0000ff]Sheet1[/COLOR]").Range("A2:B" & Sheets("[COLOR=#0000ff]Sheet1[/COLOR]").Range("A" & Rows.Count).End(xlUp).Row)
  For i = 1 To UBound(a)
    If a(i, 1) = "[COLOR=#008000]Alpha[/COLOR]" Then arrList.Add CStr(a(i, 2))
  Next
  arrList.Sort
  Sheets("[COLOR=#0000ff]Sheet2[/COLOR]").Range("A2").Resize(1, arrList.Count).Value = arrList.toArray
End Sub

I forgot to menton that I would like to have duplicates removed in the newly created row.

Can this be integrated into the above?

Thanks.