Arrannge data in a excel table

[murari28]

Hello Experts

I have data in excel which comes like product model is written at the top of a column and product serial numbers below it, The second column's header is color and color names below it. This kind of data are for multiple number of models hence can't predict the number of data columns. I need the to be stacked model wise to simplify in three columns 1st as model name, 2nd as serial number and 3rd as Color.

	The data Comes in									Need Like this
	Model1	Color	Model2	Color	Model3	Color	MOdel4	Color		Model	Serial No.	Color
	1	Black	21	Black	41	Green	61	Black		Model1	445635011569378	Black
	2	Black	22	Black	42	Green	62	Black		Model1	695442316016730	Black
	3	Black	23	Black	43	Green	63	Black		Model1	872194635375407	Black
	4	Black	24	Black	44	Green	64	Black		Model1	888543778254774	Black
	5	Black	25	Black	45	Green	65	Black		Model1	419002119059547	Black
	6	Black	26	Black	46	Green	66	Black		Model1	898705496355513	Black
	7	Black	27	Black	47	Green	67	Black		Model1	387359515924780	Black
	8	Black	28	Black	48	Green	68	Black		Model1	838179760844080	Black
	9	Black	29	Black	49	Green	69	Black		Model1	587738572745312	Black
	10	Black	30	Black	50	Green	70	Black		Model1	467907164619356	Black
	11	Green	31	Blue	51	Black	71	Gold		Model1	902393463496469	Green
	12	Green	32	Blue	52	Black	72	Gold		Model1	783556675137366	Green
	13	Green	33	Blue	53	Black	73	Gold		Model1	747932730314419	Green
	14	Green	34	Blue	54	Black	74	Gold		Model1	470800692415356	Green
	15	Green	35	Blue	55	Black	75	Gold		Model1	451979767688421	Green
	16	Green	36	Blue	56	Black	76	Gold		Model1	362567558649947	Green
	17	Green	37	Blue	57	Black	77	Gold		Model1	902479050803404	Green
	18	Green	38	Blue	58	Black	78	Gold		Model1	547605102883581	Green
	19	Green	39	Blue	59	Black	79	Gold		Model1	520003026154996	Green
	20	Green	40	Blue	60	Black	80	Gold		Model1	529906484901096	Green
										Model2	869013354652431	Black
										Model2	460297059261934	Black
										Model2	638845026339707	Black
										Model2	534138346561508	Black
										Model2	640467948443007	Black
										Model2	342765474980329	Black
										Model2	794954573800445	Black
										Model2	391917911568596	Black
										Model2	628736548741769	Black
										Model2	857939787627368	Black
										Model2	546352082692704	Blue
										Model2	828384838590137	Blue
										Model2	703976970494554	Blue
										Model2	752220122178935	Blue
										Model2	807887096411352	Blue
										Model2	551608197635321	Blue
										Model2	673480160758903	Blue
										Model2	707083664935778	Blue
										Model2	670510076297621	Blue
										Model2	369760067514220	Blue

Request experts to please help me.

Thanks in advance

 

[JEC]

Make sure your data starts in A1.

Sub jec()
  ar = Sheets(1).Cells(1).CurrentRegion
  ReDim jv((UBound(ar) - 1) * UBound(ar, 2) / 2, 3)
           
   For i = 1 To UBound(jv)
      x = (i - 1) Mod (UBound(ar) - 1) + 1
      y = 2 * ((i - 1) \ (UBound(ar) - 1)) + 1
      
      jv(i - 1, 0) = ar(1, y)
      jv(i - 1, 1) = ar(x + 1, y)
      jv(i - 1, 2) = ar(x, y + 1)
   Next
          
   Sheets(1).Cells(1, 15).Resize(UBound(jv), 3) = jv
End Sub

 

[alansidman]

With Power Query which is now called Get and Transform Data in your version and is found on the Data Tab, here is the Mcode to unpivot your data

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Model1", Int64.Type}, {"Color", type text}, {"Model2", Int64.Type}, {"Color2", type text}, {"Model3", Int64.Type}, {"Color3", type text}, {"MOdel4", Int64.Type}, {"Color4", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Color", each if Text.StartsWith([Attribute],"Color") then [Value] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom",{"Color"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each not Text.StartsWith([Attribute], "Color")),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Attribute", Order.Ascending}}),
    #"Renamed Columns" = Table.RenameColumns(#"Sorted Rows",{{"Value", "Serial"}})
in
    #"Renamed Columns"

 

[Alex Blakenburg]

@alansidman - you forgot to remove the Changed Type step.

 

[murari28]

With Power Query which is now called Get and Transform Data in your version and is found on the Data Tab, here is the Mcode to unpivot your data

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Model1", Int64.Type}, {"Color", type text}, {"Model2", Int64.Type}, {"Color2", type text}, {"Model3", Int64.Type}, {"Color3", type text}, {"MOdel4", Int64.Type}, {"Color4", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Color", each if Text.StartsWith([Attribute],"Color") then [Value] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom",{"Color"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each not Text.StartsWith([Attribute], "Color")),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Attribute", Order.Ascending}}),
    #"Renamed Columns" = Table.RenameColumns(#"Sorted Rows",{{"Value", "Serial"}})
in
    #"Renamed Columns"

Thank you very very much for your help

My problem is solved

Thank you very much again........

 

[Alex Blakenburg]

This kind of data are for multiple number of models hence can't predict the number of data columns.

If you haven't already, just make sure you delete the Changed type step, it is the only place where Models1 to 4 are currently hard coded.

 

[murari28]

Make sure your data starts in A1.

Sub jec()
  ar = Sheets(1).Cells(1).CurrentRegion
  ReDim jv((UBound(ar) - 1) * UBound(ar, 2) / 2, 3)
          
   For i = 1 To UBound(jv)
      x = (i - 1) Mod (UBound(ar) - 1) + 1
      y = 2 * ((i - 1) \ (UBound(ar) - 1)) + 1
     
      jv(i - 1, 0) = ar(1, y)
      jv(i - 1, 1) = ar(x + 1, y)
      jv(i - 1, 2) = ar(x, y + 1)
   Next
         
   Sheets(1).Cells(1, 15).Resize(UBound(jv), 3) = jv
End Sub

Thank you very very much for helping me..........

 

[murari28]

If you haven't already, just make sure you delete the Changed type step, it is the only place where Models1 to 4 are currently hard coded.

Thank you very much for your suggestion.........