Vba to transport rows to columns- Set of data

[aravindhan_31]

Hi,

coming back after a long time coz i need your help very badly.

I have 2 columns like below, I simply need to to transpose these data into columns in a new sheet


List 1 List 2
Apple Boomerang
Apple Offerings
Apple Industrial Equipment
Apple Chemicals & Natural Resources
Apple Consumer Goods & Services (CGS)
Apple Air Freight Logistics&Travel Srvs(AF&TS)
Apple Life Sciences (LS)
Apple Capital Markets
Apple Utilities
Orange Insurance
Orange Banking
Orange Media & Entertainment
Orange Electronics & High Tech
Mango Communications
Mango Data Science Practice
Mango Industry Value Team
Mango Global Capability
Mango Platform Sales
Mango Data Engineering Practice


eg Col 1 with header Apple, Col 2 with header Orange, Col 3 with header Mango
and in each of those headers I need values that are there in List 2. that is under Apple i need like below

Apple
Boomerang
Offering
Industrial Equipment
Chemicals & Natural Resources
Consumer Goods & Services (CGS)
Air Freight Logistics&Travel Srvs(AF&TS)
Life Sciences (LS)
Capital Markets
Utilities

like this i need to create columns for all values from List 1, and paste data as per List 2. If i do this in pivot i get in all in one below the other.

regards
Arvind

 

[MickG]

Try this for Results on sheet2.

Sub MG23Jan21
Dim Rng As Range, Dn As Range, n As Long, K As Variant, col As Long

Set Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
For Each Dn In Rng
    If Not .Exists(Dn.Value) Then
        .Add Dn.Value, Dn.Offset(, 1)
    Else
        Set .Item(Dn.Value) = Union(.Item(Dn.Value), Dn.Offset(, 1))
    End If
Next

For Each K In .keys
    col = col + 1
    Sheets("Sheet2").Cells(1, col) = K
    Sheets("Sheet2").Cells(2, col).Resize(.Item(K).Count).Value = .Item(K).Value
Next K
End With
    Sheets("Sheet2").Columns("A").Resize(, col).Columns.AutoFit
End Sub

Regards Mick

 

[aravindhan_31]

Works perfectly, thank you so much. You are a star.

quick add on, can we define Names for the Ranges? eg, A2 to A99 in sheet 2 to be "Named" as Apple, B2 to B5 to be names as "Orange" & c2 to C7 as Mango.

I tried with excel option " Create from Selection" & Name as per "Top Row", it create names for rows 2 to 9 for all , Apples, Mango & Orange, with blanks, which i dont need.

do you think you can help me

Regards
Arvind

 

[MickG]

Perhaps this:-
I have created the named ranges for the length of data in each column, if you require each named range to be of other specific lengths, let me know.
Add line as shown in red

For Each K In .keys
    col = col + 1
    Sheets("Sheet2").Cells(1, col) = K
    Sheets("Sheet2").Cells(2, col).Resize(.Item(K).Count).Value = .Item(K).Value
    Sheets("Sheet2").Cells(2, col).Resize(.Item(K).Count).Name = K
Next K

 

[aravindhan_31]

sorry for replying late, went on vacation and couldn't check this.

this one worked exactly the way i wanted.

thanks

Regards
Arvind

 

[MickG]

You're welcome

 

[JosephKiran]

Hi Mick

This is not working when there is a space between 2 words in column A.

Ex:

List 1	List 2
Apple Grapes	Boomerang
Apple Grapes	Offerings
Apple Grapes	Industrial Equipment
Apple Grapes	Chemicals & Natural Resources
Apple Grapes	Consumer Goods & Services (CGS)
Apple Grapes	Air Freight Logistics&Travel Srvs(AF&TS)
Apple Grapes	Life Sciences (LS)
Apple Grapes	Capital Markets
Apple Grapes	Utilities
Orange Grapes	Insurance
Orange Grapes	Banking
Orange Grapes	Media & Entertainment
Orange Grapes	Electronics & High Tech
Mango Grapes	Communications
Mango Grapes	Data Science Practice
Mango Grapes	Industry Value Team
Mango Grapes	Global Capability
Mango Grapes	Platform Sales
Mango Grapes	Data Engineering Practice
Mango Grapes	Data Engineering Practice
Mango Grapes	Data Engineering Practice
Mango Grapes	Data Engineering Practice

 

[Fluff]

Use

    Sheets("Sheet2").Cells(2, col).Resize(.Item(K).Count).Name = Replace(K, " ", "_")

 

[JosephKiran]

It is not working for the cases where i have multiple spaces like this:

Chemicals & Natural Resources
Corporate Marketing & Communications
Air Freight Logistics&Travel Srvs(AF&TS)

 

[Fluff]

That's got nothing to do with the spaces, it's the fact that you have illegal characters.
The rules for names are

• The first characterof a name must be one of the following characters:
  • letter
  • underscore (_)
  • backslash (\).
• Remaining characters in the name can be
  • letters
  • numbers
  • periods
  • underscore characters
• The following are not allowed:
  • Space characters are not allowed as part of a name.
  • Names can't look like cell addresses, such as A$35 or R2D2
  • C, c, R, r -- can't be used as names -- Excel uses them as selection shortcuts

from

What Are the Rules for Excel Names? - Contextures Blog

As Juliet famously said to Romeo, "What's in a name?" And she was talking about rows (misspelled as "rose"), so maybe Juliet was using a spreadsheet at the

contexturesblog.com