Row Population and Transposing of data

ibesmond · Jun 11, 2020

Hello

I have a data set that has some problems and needs cleanup, but I'm not sure how to accomplish this feat.

Basically I want to flatten the data as much as possible without loosing any data.
The data came from different sources so each row represents a row, and the column is where the data is located.
If This was a small set I would just go manually, but this is only a small subset of the data.
The sources are in no particular order.

The first data set is problem.
The second data set is solution 1. If I could get the data to this position I would be happy, but
The third tab is solution 2, if I could get it to this stage that would be the ultimate goal.

Name	Genre	Born Date	Died Date	Born Location	Died Location	Parents	Favorite Songs	Cause of death	Religion	Movies
Bob Marley	Reggae
Bob Marley								Melanoma	Rastafari	Marley
Bob Marley						Norval Marley, Cedella Booker	Corner Stone
Bob Marley		2/6/1945	5/11/1981	Nine Mile, Jamaica	Miami, FL
Bob Marley							Redemption Song
Buju Banton	Reggae
Buju Banton		7/15/1973		Kingston, Jamaica
Buju Banton							Untold Stories
Bunny Wailer	Reggae
Bunny Wailer		4/10/1947		Kingston, Jamaica
Bunny Wailer							Cool Runnings
Burning Spear	Reggae						Old Marcus Garvey
Burning Spear		3/1/1945		St. Ann Parish, Jamaica
Damian Marley	Reggae
Damian Marley		7/21/1978		Kingston, Jamaica
Damian Marley						Bob Marley, Cindy Breakspeare	Welcome to Jam Rock
Damian Marley							Medication
Dennis Brown
Dennis Brown	Reggae							Pneumothorax
Dennis Brown							The Promised Land
Dennis Brown		2/1/1957	7/1/1999	Kingston, Jamaica	Kingston, Jamaica
Desmend Dekker	Reggae
Desmend Dekker		7/16/1941	5/25/2006	St. Andrew Parish, Jamaica	Thornton Heath, United Kingdom
Desmend Dekker							Isrealites
Jimmy Cliff	Reggae
Jimmy Cliff		4/1/1948		St. James Parish, Jamaica
Jimmy Cliff							I can See Clearly Now
Jimmy Cliff
Lee "Scratch" Perry	Reggae
Lee "Scratch" Perry		3/20/1936		Kendal, Jamaica
Lee "Scratch" Perry							Chase the Devil			Lee Scratch Perry's Vision of Paradise
Lee "Scratch" Perry
Peter Tosh	Reggae
Peter Tosh							You Can't Blame the Youth
Peter Tosh		10/19/1944	9/11/1987	Grange Hill, Jamaica	Kingston, Jamaica
Peter Tosh								Murder	Rastafari	Stepping Razor: Red X
Peter Tosh							Legalize it
Peter Tosh							Glass House
Peter Tosh
Stephen Marley	Reggae
Stephen Marley		5/19/1972		Wilmington, DE
Stephen Marley							Mind Control
Stephen Marley							Medication
Stephen Marley							Chase Dem
Stephen Marley							Iron Bars
Stephen Marley							Inna Di Red
Ziggy Marley	Reggae
Ziggy Marley		10/17/1968		Kingston, Jamaica
Ziggy Marley							Looking
Ziggy Marley							True to Myself
Ziggy Marley							Dragonfly
Ziggy Marley							Keep My Faith
Ziggy Marley										Shark Tale
Ziggy Marley										Life and Debt
Ziggy Marley										I Am Bolt
Ziggy Marley										Pup Star
Ziggy Marley										The Strink is In

2nd Set

Name	Genre	Born Date	Died Date	Born Location	Died Location	Parents	Favorite Songs	Cause of death	Religion	Movies
Bob Marley	Reggae	2/6/1945	5/11/1981	Nine Mile, Jamaica	Miami, FL	Norval Marley, Cedella Booker	Corner Stone	Melanoma	Rastafari	Marley
Bob Marley	Reggae	2/6/1945	5/11/1981	Nine Mile, Jamaica	Miami, FL	Norval Marley, Cedella Booker	Redemption Song	Melanoma	Rastafari	Marley
Buju Banton	Reggae	7/15/1973		Kingston, Jamaica			Untold Stories
Bunny Wailer	Reggae	4/10/1947		Kingston, Jamaica			Cool Runnings
Burning Spear	Reggae	3/1/1945		St. Ann Parish, Jamaica			Old Marcus Garvey
Damian Marley	Reggae	7/21/1978		Kingston, Jamaica		Bob Marley, Cindy Breakspeare	Welcome to Jam Rock
Damian Marley	Reggae	7/21/1978		Kingston, Jamaica		Bob Marley, Cindy Breakspeare	Medication
Dennis Brown	Reggae	2/1/1957	7/1/1999	Kingston, Jamaica	Kingston, Jamaica		The Promised Land	Pneumothorax
Desmend Dekker	Reggae	7/16/1941	5/25/2006	St. Andrew Parish, Jamaica	Thornton Heath, United Kingdom		Isrealites
Jimmy Cliff	Reggae	4/1/1948		St. James Parish, Jamaica			I can See Clearly Now
Lee "Scratch" Perry	Reggae	3/20/1936		Kendal, Jamaica			Chase the Devil			Lee Scratch Perry's Vision of Paradise
Peter Tosh	Reggae	10/19/1944	9/11/1987	Grange Hill, Jamaica	Kingston, Jamaica		You Can't Blame the Youth	Murder	Rastafari	Stepping Razor: Red X
Peter Tosh	Reggae	10/19/1944	9/11/1987	Grange Hill, Jamaica	Kingston, Jamaica		Legalize it	Murder	Rastafari	Stepping Razor: Red X
Peter Tosh	Reggae	10/19/1944	9/11/1987	Grange Hill, Jamaica	Kingston, Jamaica		Glass House	Murder	Rastafari	Stepping Razor: Red X
Stephen Marley	Reggae	5/19/1972		Wilmington, DE			Mind Control
Stephen Marley	Reggae	5/19/1972		Wilmington, DE			Medication
Stephen Marley	Reggae	5/19/1972		Wilmington, DE			Chase Dem
Stephen Marley	Reggae	5/19/1972		Wilmington, DE			Iron Bars
Stephen Marley	Reggae	5/19/1972		Wilmington, DE			Inna Di Red
Ziggy Marley	Reggae	10/17/1968		Kingston, Jamaica			Looking			Shark Tale
Ziggy Marley	Reggae	10/17/1968		Kingston, Jamaica			True to Myself			Life and Debt
Ziggy Marley	Reggae	10/17/1968		Kingston, Jamaica			Dragonfly			I Am Bolt
Ziggy Marley	Reggae	10/17/1968		Kingston, Jamaica			Keep My Faith			Pup Star
Ziggy Marley	Reggae	10/17/1968		Kingston, Jamaica			Keep My Faith			The Strink is In

3rd Set

Name

Genre

Born Date

Died Date

Born Location

Died Location

Parents

Favorite Songs1

Favorite Songs2

Favorite Songs3

Favorite Songs4

Favorite Songs5

Cause of death

Religion

Movies1

Movies12

Movies3

Movies4

Movies5

Bob Marley

Reggae

2/6/1945

5/11/1981

Nine Mile, Jamaica

Miami, FL

Norval Marley, Cedella Booker

Corner Stone

Redemption Song

Melanoma

Rastafari

Marley

Buju Banton

Reggae

7/15/1973

Kingston, Jamaica

Untold Stories

Bunny Wailer

Reggae

4/10/1947

Kingston, Jamaica

Cool Runnings

Burning Spear

Reggae

3/1/1945

St. Ann Parish, Jamaica

Old Marcus Garvey

Damian Marley

Reggae

7/21/1978

Kingston, Jamaica

Bob Marley, Cindy Breakspeare

Welcome to Jam Rock

Medication

Dennis Brown

Reggae

2/1/1957

7/1/1999

Kingston, Jamaica

The Promised Land

Pneumothorax

Desmend Dekker

Reggae

7/16/1941

5/25/2006

St. Andrew Parish, Jamaica

Thornton Heath, United Kingdom

Isrealites

Jimmy Cliff

Reggae

4/1/1948

St. James Parish, Jamaica

I can See Clearly Now

Marley

Lee "Scratch" Perry

Reggae

3/20/1936

Kendal, Jamaica

Chase the Devil

Lee Scratch Perry's Vision of Paradise

Peter Tosh

Reggae

10/19/1944

9/11/1987

Grange Hill, Jamaica

Kingston, Jamaica

You Can't Blame the Youth

Legalize it

Glass House

Murder

Rastafari

Stepping Razor: Red X

Stephen Marley

Reggae

5/19/1972

Wilmington, DE

Mind Control

Medication

Chase Dem

Iron Bars

Inna Di Red

Ziggy Marley

Reggae

10/17/1968

Kingston, Jamaica

Looking

True to Myself

Dragonfly

Keep My Faith

Shark Tale

Life and Debt

I Am Bolt

Pup Star

The Strink is In

I appreciate the advise. Thank you

sandy666 · Jun 11, 2020

something like this or you need to split songs and movies?

lrobbo314 · Jun 11, 2020

Looks familiar.

Book2

A

B

C

D

E

F

G

H

I

J

K

1

Name

Genre

Born Date

Died Date

Born Location

Died Location

Parents

Favorite Songs

Cause of death

Religion

Movies

2

Bob Marley

Reggae

2/6/1945

5/11/1981

Nine Mile, Jamaica

Miami, FL

Norval Marley, Cedella Booker

Corner Stone,Redemption Song

Melanoma

Rastafari

Marley

3

Buju Banton

Reggae

7/15/1973

Kingston, Jamaica

Untold Stories

4

Bunny Wailer

Reggae

4/10/1947

Kingston, Jamaica

Cool Runnings

5

Burning Spear

Reggae

3/1/1945

St. Ann Parish, Jamaica

Old Marcus Garvey

6

Damian Marley

Reggae

7/21/1978

Kingston, Jamaica

Bob Marley, Cindy Breakspeare

Welcome to Jam Rock,Medication

7

Dennis Brown

Reggae

2/1/1957

7/1/1999

Kingston, Jamaica

The Promised Land

Pneumothorax

8

Desmend Dekker

Reggae

7/16/1941

5/25/2006

St. Andrew Parish, Jamaica

Thornton Heath, United Kingdom

Isrealites

9

Jimmy Cliff

Reggae

4/1/1948

St. James Parish, Jamaica

I can See Clearly Now

10

Lee "Scratch" Perry

Reggae

3/20/1936

Kendal, Jamaica

Chase the Devil

Lee Scratch Perry's Vision of Paradise

11

Peter Tosh

Reggae

10/19/1944

9/11/1987

Grange Hill, Jamaica

Kingston, Jamaica

You Can't Blame the Youth,Legalize it,Glass House

Murder

Rastafari

Stepping Razor: Red X

12

Stephen Marley

Reggae

5/19/1972

Wilmington, DE

Mind Control,Medication,Chase Dem,Iron Bars,Inna Di Red

13

Ziggy Marley

Reggae

10/17/1968

Kingston, Jamaica

Looking,True to Myself,Dragonfly,Keep My Faith

Shark Tale,Life and Debt,I Am Bolt,Pup Star,The Strink is In

Sheet4

sandy666 · Jun 11, 2020

it's only a matter of delimiter

imho, after comma should be a space

lrobbo314 · Jun 11, 2020

Agreed. I saw that after posting and it made the OCD go off.

sandy666 · Jun 11, 2020

to tell the truth these are insignificant details

DanteAmor · Jun 11, 2020

Here a macro for you to consider.

Your data on "set1" sheet starting at A1, results on "set3" sheet.
If the songs or movies are more or less than 5, the macro automatically adjusts the columns.

VBA Code:

Sub Row_Population_Transposing_Data()
  Dim a As Variant, b As Variant, c As Variant, d As Variant, e As Variant
  Dim dic As Object, sh As Worksheet
  Dim i As Long, j As Long, k As Long, lr As Long, m As Long, n As Long, u As Long
 
  Set sh = Sheets("Set1")
  With sh
    lr = .Range("A" & Rows.Count).End(3).Row
    a = .Range("A2:K" & lr).Value2
    m = Evaluate(Replace("=MAX(COUNTIFS(@,@," & .Name & "!H2:H" & lr & ",""<>""))", "@", .Name & "!A2:A" & lr)) 'max songs
    n = Evaluate(Replace("=MAX(COUNTIFS(@,@," & .Name & "!K2:K" & lr & ",""<>""))", "@", .Name & "!A2:A" & lr)) 'max movies
    u = Evaluate(Replace("=SUMPRODUCT((@<>"""")/COUNTIF(@,@&""""))", "@", .Name & "!A2:A" & lr)) 'unique
  End With
  ReDim b(1 To u, 1 To 7)
  ReDim c(1 To u, 1 To m)
  ReDim d(1 To u, 1 To 2)
  ReDim e(1 To u, 1 To n)
  Set dic = CreateObject("Scripting.Dictionary")
 
  For i = 1 To UBound(a, 1)
    If Not dic.exists(a(i, 1)) Then
      j = j + 1
      dic(a(i, 1)) = j
    End If
    j = dic(a(i, 1))
    For k = 1 To 7
      If a(i, k) <> "" Then b(j, k) = a(i, k)
    Next
    If a(i, 8) <> "" Then
      For k = 1 To m
        If c(j, k) = "" Then
          c(j, k) = a(i, 8)
          Exit For
        End If
      Next
    End If
    For k = 1 To 2
      If a(i, k + 8) <> "" Then d(j, k) = a(i, k + 8)
    Next
    If a(i, 11) <> "" Then
      For k = 1 To n
        If e(j, k) = "" Then
          e(j, k) = a(i, 11)
          Exit For
        End If
      Next
    End If
  Next
 
  With Sheets("Set3")
    'Headears
    .Range("A1").Resize(1, 7).Value = sh.Range("A1:G1").Value
    .Range("H1").Resize(1, m).Value = sh.Range("H1").Value
    .Cells(1, 8 + m).Resize(1, 2).Value = sh.Range("I1:J1").Value
    .Cells(1, 8 + m + 2).Resize(1, n).Value = sh.Range("K1").Value
    'Data
    .Range("A2").Resize(u, 7).Value = b
    .Range("H2").Resize(u, m).Value = c
    .Cells(2, 8 + m).Resize(u, 2).Value = d
    .Cells(2, 8 + m + 2).Resize(u, n).Value = e
  End With
End Sub

sandy666 · Jun 12, 2020



Name	Genre	Born Date	Died Date	Born Location	Died Location	Parents	Favorite Songs.1	Favorite Songs.2	Favorite Songs.3	Favorite Songs.4	Favorite Songs.5	Cause of death	Religion	Movies.1	Movies.2	Movies.3	Movies.4	Movies.5
Bob Marley	Reggae	2/6/1945	5/11/1981	Nine Mile, Jamaica	Miami, FL	Norval Marley, Cedella Booker	Corner Stone	Redemption Song				Melanoma	Rastafari	Marley
Buju Banton	Reggae	7/15/1973		Kingston, Jamaica			Untold Stories
Bunny Wailer	Reggae	4/10/1947		Kingston, Jamaica			Cool Runnings
Burning Spear	Reggae	3/1/1945		St. Ann Parish, Jamaica			Old Marcus Garvey
Damian Marley	Reggae	7/21/1978		Kingston, Jamaica		Bob Marley, Cindy Breakspeare	Welcome to Jam Rock	Medication
Dennis Brown	Reggae	2/1/1957	7/1/1999	Kingston, Jamaica	Kingston, Jamaica		The Promised Land					Pneumothorax
Desmend Dekker	Reggae	7/16/1941	5/25/2006	St. Andrew Parish, Jamaica	Thornton Heath, United Kingdom		Isrealites
Jimmy Cliff	Reggae	4/1/1948		St. James Parish, Jamaica			I can See Clearly Now
Lee "Scratch" Perry	Reggae	3/20/1936		Kendal, Jamaica			Chase the Devil							Lee Scratch Perry's Vision of Paradise
Peter Tosh	Reggae	10/19/1944	9/11/1987	Grange Hill, Jamaica	Kingston, Jamaica		You Can't Blame the Youth	Legalize it	Glass House			Murder	Rastafari	Stepping Razor: Red X
Stephen Marley	Reggae	5/19/1972		Wilmington, DE			Mind Control	Medication	Chase Dem	Iron Bars	Inna Di Red
Ziggy Marley	Reggae	10/17/1968		Kingston, Jamaica			Looking	True to Myself	Dragonfly	Keep My Faith				Shark Tale	Life and Debt	I Am Bolt	Pup Star	The Strink is In

Rich (BB code):

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Name"}, {{"Count", each _, type table}}),
    GenreExtract = Table.TransformColumns(Table.AddColumn(Group, "Genre", each [Count][Genre]), {"Genre", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    BornDateExtract = Table.TransformColumns(Table.AddColumn(GenreExtract, "Born Date", each [Count][Born Date]), {"Born Date", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    DiedDateExtract = Table.TransformColumns(Table.AddColumn(BornDateExtract, "Died Date", each [Count][Died Date]), {"Died Date", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    BornLocationExtract = Table.TransformColumns(Table.AddColumn(DiedDateExtract, "Born Location", each [Count][Born Location]), {"Born Location", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    DiedLocationExtract = Table.TransformColumns(Table.AddColumn(BornLocationExtract, "Died Location", each [Count][Died Location]), {"Died Location", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    ParentsExtract = Table.TransformColumns(Table.AddColumn(DiedLocationExtract, "Parents", each [Count][Parents]), {"Parents", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    SongsExtract = Table.TransformColumns(Table.AddColumn(ParentsExtract, "Favorite Songs", each [Count][Favorite Songs]), {"Favorite Songs", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    MaxCount1 = List.Max(Table.AddColumn(SongsExtract, "SCount", each List.Count(Text.Split([Favorite Songs],",")))[SCount]),
    SplitTCSongs = Table.SplitColumn(SongsExtract, "Favorite Songs", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), MaxCount1),
    CauseExtract = Table.TransformColumns(Table.AddColumn(SplitTCSongs, "Cause of death", each [Count][Cause of death]), {"Cause of death", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    ReligionExtract = Table.TransformColumns(Table.AddColumn(CauseExtract, "Religion", each [Count][Religion]), {"Religion", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    MoviesExtract = Table.TransformColumns(Table.AddColumn(ReligionExtract, "Movies", each [Count][Movies]), {"Movies", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    MaxCount2 = List.Max(Table.AddColumn(MoviesExtract, "SCount", each List.Count(Text.Split([Movies],",")))[SCount]),
    SplitTCMovies = Table.SplitColumn(MoviesExtract, "Movies", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), MaxCount2)
in
    SplitTCMovies

lrobbo314 · Jun 12, 2020

Here is my version.

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

1

Name

Genre

Born Date

Died Date

Born Location

Died Location

Parents

Favorite Songs.1

Favorite Songs.2

Favorite Songs.3

Favorite Songs.4

Favorite Songs.5

Cause of death

Religion

Movies.1

Movies.2

Movies.3

Movies.4

Movies.5

2

Bob Marley

Reggae

2/6/1945

5/11/1981

Nine Mile, Jamaica

Miami, FL

Norval Marley, Cedella Booker

Corner Stone

Redemption Song

Melanoma

Rastafari

Marley

3

Buju Banton

Reggae

7/15/1973

Kingston, Jamaica

Untold Stories

4

Bunny Wailer

Reggae

4/10/1947

Kingston, Jamaica

Cool Runnings

5

Burning Spear

Reggae

3/1/1945

St. Ann Parish, Jamaica

Old Marcus Garvey

6

Damian Marley

Reggae

7/21/1978

Kingston, Jamaica

Bob Marley, Cindy Breakspeare

Welcome to Jam Rock

Medication

7

Dennis Brown

Reggae

2/1/1957

7/1/1999

Kingston, Jamaica

The Promised Land

Pneumothorax

8

Desmend Dekker

Reggae

7/16/1941

5/25/2006

St. Andrew Parish, Jamaica

Thornton Heath, United Kingdom

Isrealites

9

Jimmy Cliff

Reggae

4/1/1948

St. James Parish, Jamaica

I can See Clearly Now

10

Lee "Scratch" Perry

Reggae

3/20/1936

Kendal, Jamaica

Chase the Devil

Lee Scratch Perry's Vision of Paradise

11

Peter Tosh

Reggae

10/19/1944

9/11/1987

Grange Hill, Jamaica

Kingston, Jamaica

You Can't Blame the Youth

Legalize it

Glass House

Murder

Rastafari

Stepping Razor: Red X

12

Stephen Marley

Reggae

5/19/1972

Wilmington, DE

Mind Control

Medication

Chase Dem

Iron Bars

Inna Di Red

13

Ziggy Marley

Reggae

10/17/1968

Kingston, Jamaica

Looking

True to Myself

Dragonfly

Keep My Faith

Shark Tale

Life and Debt

I Am Bolt

Pup Star

The Strink is In

Sheet2

Rich (BB code):

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Name"}, {
        {"Genre", each Text.Combine(_[Genre],", "), type text},
        {"Born Date", each Text.Combine(_[Born Date],", "), type text},
        {"Died Date", each Text.Combine(_[Died Date],", "), type text},
        {"Born Location", each Text.Combine(_[Born Location],", "), type text},
        {"Died Location", each Text.Combine(_[Died Location],", "), type text},
        {"Parents", each Text.Combine(_[Parents],", "), type text},
        {"Favorite Songs", each Text.Combine(_[Favorite Songs],", "), type text},
        {"Cause of death", each Text.Combine(_[Cause of death],", "), type text},
        {"Religion", each Text.Combine(_[Religion],", "), type text},
        {"Movies", each Text.Combine(_[Movies],", "), type text}
        }),
    SplitSongs = Table.SplitColumn(Group, "Favorite Songs", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), {"Favorite Songs.1", "Favorite Songs.2", "Favorite Songs.3", "Favorite Songs.4", "Favorite Songs.5"}),
    SplitMovies = Table.SplitColumn(SplitSongs, "Movies", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), {"Movies.1", "Movies.2", "Movies.3", "Movies.4", "Movies.5"})
in
    SplitMovies

sandy666 · Jun 12, 2020

You can make it a bit shorter with count delimiter and maxcount

Rich (BB code):

SplitSongs = Table.SplitColumn(Group, "Favorite Songs", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), {"Favorite Songs.1", "Favorite Songs.2", "Favorite Songs.3", "Favorite Songs.4", "Favorite Songs.5"}),

to

Rich (BB code):

SplitSongs = Table.SplitColumn(Group, "Favorite Songs", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), MaxCount),

Row Population and Transposing of data

ibesmond

New Member

Excel Facts

sandy666

Banned - Rules violations

lrobbo314

Well-known Member

sandy666

Banned - Rules violations

lrobbo314

Well-known Member

sandy666

Banned - Rules violations

DanteAmor

Well-known Member

sandy666

Banned - Rules violations

lrobbo314

Well-known Member

sandy666

Banned - Rules violations

Forum statistics

Share this page

Row Population and Transposing of data

New Member

Excel Facts

Banned - Rules violations

Well-known Member

Banned - Rules violations

Well-known Member

Banned - Rules violations

Well-known Member

Banned - Rules violations

Well-known Member

Banned - Rules violations

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock