How to transpose uneven data

I

InfinityMrsn

New Member
Joined
Mar 21, 2023
Messages
15
Office Version
  1. 365
Platform
  1. Windows
Hi everyone,

I have a list of a lot of data what I want to transpose. This works pretty well, but I have uneven data.

For example: I have up to 18 different properties, but not every item has the same number of properties.
There are products with 1-18 properties, but also products with 1-13 properties or products with properties 1,3,5,7,8,13,14,15,16,17,18.

On sheet1 you will find the data (shortened), on sheet 2 you will find how the final result should be.

I have a VBA code, but it ignores the odd properties, so information gets mixed up.

I hope someone could help me with it.

Sheet 1:
Titlemb1Likepx1textgreen500Field2FieldField1
ABBA Straps BCA28ABBABCA2847,-StockSize22 mm
ABBA Straps BCA28ABBABCA2847,-StockMaterialLeer
ABBA Straps BCA28ABBABCA2847,-StockSize22 mm
ABBA Straps BCA28ABBABCA2847,-StockSize A22 mm
ABBA Straps BCA28ABBABCA2847,-StockColor AZwart
ABBA Straps BCA28ABBABCA2847,-StockColor CRood
ABBA Straps BCA28ABBABCA2847,-StockLockGesp
ABBA Straps BCA28ABBABCA2847,-StockColor BZilver
ABBA Straps BCA28ABBABCA2847,-Stock12U75 mm
ABBA Straps BCA28ABBABCA2847,-Stock6U120 mm
ABBA Straps BCA28ABBABCA2847,-StockOrigNee
ABBA Straps BCA28ABBABCA2847,-StockStartPushpins
ABBA Straps BCA28ABBABCA2847,-StockStraightJa
ABBA Straps BCA301ABBABCA30130,-StockSize20 mm
ABBA Straps BCA301ABBABCA30130,-StockMaterialMetaal
ABBA Straps BCA301ABBABCA30130,-StockExtra infoStainless Steel Bracelet
ABBA Straps BCA301ABBABCA30130,-StockSize20 mm
ABBA Straps BCA301ABBABCA30130,-StockColor ARoségoud
ABBA Straps BCA301ABBABCA30130,-StockLockVWS
ABBA Straps BCA301ABBABCA30130,-StockColor BRoségoud
ABBA Straps BCA301ABBABCA30130,-StockOrigNee
ABBA Straps BCA301ABBABCA30130,-StockStartPushpins
ABBA Straps BCA301ABBABCA30130,-StockStraightNee
ABBA Straps BCA22ABBABCA2246,-StockSize22 mm
ABBA Straps BCA22ABBABCA2246,-StockMaterialLeer
ABBA Straps BCA22ABBABCA2246,-StockSize22 mm
ABBA Straps BCA22ABBABCA2246,-StockSize A22 mm
ABBA Straps BCA22ABBABCA2246,-StockSize B20 mm
ABBA Straps BCA22ABBABCA2246,-StockColor AZwart
ABBA Straps BCA22ABBABCA2246,-StockColor CWit
ABBA Straps BCA22ABBABCA2246,-StockLockGesp
ABBA Straps BCA22ABBABCA2246,-StockColor BZilver
ABBA Straps BCA22ABBABCA2246,-Stock12U75 mm
ABBA Straps BCA22ABBABCA2246,-Stock6U120 mm
ABBA Straps BCA22ABBABCA2246,-StockOrigNee
ABBA Straps BCA22ABBABCA2246,-StockStartPushpins
ABBA Straps BCA22ABBABCA2246,-StockStraightJa

Sheet 2:
SizeMaterialExtra infoSizeSize ASize BColor AColor CLockColor B12U6UOrigStartStraight
ABBA Straps BCA2822 mmLeer22 mm22 mmZwartRoodGespZilver75 mm120 mmNeePushpinsJa
ABBA Straps BCA30120 mmMetaalStainless steel bracelet20 mmRoségoudVWSRoségoudNeePushpinsNee
ABBA Straps BCA2222 mmLeer22 mm22 mm20 mmZwartWitGespZilver75 mm120 mmNeePushpinsJa
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
InfinityMrsn said:
Ja
Click to expand...
You could use Power query
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column6"}),
    #"Removed Top Rows" = Table.Skip(#"Removed Columns",1),
    #"Removed Columns1" = Table.RemoveColumns(#"Removed Top Rows",{"Column2", "Column3", "Column4", "Column5"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Column7]), "Column7", "Column8", List.Max),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,"",Replacer.ReplaceValue,{"Size", "Material", "Size A", "Color A", "Color C", "Lock", "Color B", "12U", "6U", "Orig", "Start", "Straight", "Extra info", "Size B"})
in
    #"Replaced Value"
to get something like this
Book1
ABCDEFGHIJKLMNO
1Column1SizeMaterialSize AColor AColor CLockColor B12U6UOrigStartStraightExtra infoSize B
2ABBA Straps BCA2222 mmLeer22 mmZwartWitGespZilver75 mm120 mmNeePushpinsJa20 mm
3ABBA Straps BCA2822 mmLeer22 mmZwartRoodGespZilver75 mm120 mmNeePushpinsJa
4ABBA Straps BCA30120 mmMetaalRoségoudVWSRoségoudNeePushpinsNeeStainless Steel Bracelet
Table1
 
Upvote 0
Hi

Many many many!!!!
Thanks for your quick reply. Really aprecciate it.
Maybe I'm doing something wrong, but please see my results. Please see my images.
Can you tell me what im doing wrong?
q3 (1).png
q1 (1).png
q2 (1).png
 
Upvote 0
The query is written based on the data you supplied in the original question
Book1
ABCDEFGH
1Column1Column2Column3Column4Column5Column6Column7Column8
2Titlemb1Likepx1textgreen500Field2FieldField1
3ABBA Straps BCA28ABBABCA2847,-StockSize22 mm
4ABBA Straps BCA28ABBABCA2847,-StockMaterialLeer
5ABBA Straps BCA28ABBABCA2847,-StockSize22 mm
6ABBA Straps BCA28ABBABCA2847,-StockSize A22 mm
7ABBA Straps BCA28ABBABCA2847,-StockColor AZwart
8ABBA Straps BCA28ABBABCA2847,-StockColor CRood
9ABBA Straps BCA28ABBABCA2847,-StockLockGesp
10ABBA Straps BCA28ABBABCA2847,-StockColor BZilver
11ABBA Straps BCA28ABBABCA2847,-Stock12U75 mm
12ABBA Straps BCA28ABBABCA2847,-Stock6U120 mm
13ABBA Straps BCA28ABBABCA2847,-StockOrigNee
14ABBA Straps BCA28ABBABCA2847,-StockStartPushpins
15ABBA Straps BCA28ABBABCA2847,-StockStraightJa
16ABBA Straps BCA301ABBABCA30130,-StockSize20 mm
17ABBA Straps BCA301ABBABCA30130,-StockMaterialMetaal
18ABBA Straps BCA301ABBABCA30130,-StockExtra infoStainless Steel Bracelet
19ABBA Straps BCA301ABBABCA30130,-StockSize20 mm
20ABBA Straps BCA301ABBABCA30130,-StockColor ARoségoud
21ABBA Straps BCA301ABBABCA30130,-StockLockVWS
22ABBA Straps BCA301ABBABCA30130,-StockColor BRoségoud
23ABBA Straps BCA301ABBABCA30130,-StockOrigNee
24ABBA Straps BCA301ABBABCA30130,-StockStartPushpins
25ABBA Straps BCA301ABBABCA30130,-StockStraightNee
26ABBA Straps BCA22ABBABCA2246,-StockSize22 mm
27ABBA Straps BCA22ABBABCA2246,-StockMaterialLeer
28ABBA Straps BCA22ABBABCA2246,-StockSize22 mm
29ABBA Straps BCA22ABBABCA2246,-StockSize A22 mm
30ABBA Straps BCA22ABBABCA2246,-StockSize B20 mm
31ABBA Straps BCA22ABBABCA2246,-StockColor AZwart
32ABBA Straps BCA22ABBABCA2246,-StockColor CWit
33ABBA Straps BCA22ABBABCA2246,-StockLockGesp
34ABBA Straps BCA22ABBABCA2246,-StockColor BZilver
35ABBA Straps BCA22ABBABCA2246,-Stock12U75 mm
36ABBA Straps BCA22ABBABCA2246,-Stock6U120 mm
37ABBA Straps BCA22ABBABCA2246,-StockOrigNee
38ABBA Straps BCA22ABBABCA2246,-StockStartPushpins
39ABBA Straps BCA22ABBABCA2246,-StockStraightJa
Sheet1
 
Upvote 0
@Kerryx When i want to use the query in my original file i get a error "Expression.Error: Er is geen Excel-tabel met de naam Table1 gevonden."
 
Upvote 0
What is the name of the Table for your source data, you need to change the source is the Table is not called Table1

Power Query: 
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content]
 
Upvote 1
I am sure your help works 1000% .. This is on me!

I found out i need to translate some parts. If i can get it right i'll come back to you.
 
Upvote 0
@Kerryx almost there :)


Expression.Error: Kan operator < niet toepassen op typen Table en Table.
Details:
Operator=<
Left=

Right=
 

Attachments

  • q4 (1).jpg
    q4 (1).jpg
    207.2 KB · Views: 2
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
How to give remark based on specific sequence number?
Replies
5
Views
238
Peter_SSs
Peter_SSs
M
  • Question
Struggling with Variants within my data
Replies
1
Views
408
Mr Young
M
T
  • Question
External Schedule integrate in to Excel
Replies
0
Views
301
Tofik
T
Y
  • Question
Macro to copy values across rows and transposing them and add the user id
2
Replies
17
Views
2K
DanteAmor
DanteAmor
S
  • Question
Create a ToggleButton
2
Replies
10
Views
2K
bobsan42
bobsan42

Forum statistics

Threads
1,215,440
Messages
6,124,882
Members
449,193
Latest member
PurplePlop

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top