How to transpose uneven data

[InfinityMrsn]

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:

Title	mb1	Like	px1	textgreen500	Field2	Field	Field1
ABBA Straps BCA28	ABBA	BCA28	47,-	Stock		Size	22 mm
ABBA Straps BCA28	ABBA	BCA28	47,-	Stock		Material	Leer
ABBA Straps BCA28	ABBA	BCA28	47,-	Stock		Size	22 mm
ABBA Straps BCA28	ABBA	BCA28	47,-	Stock		Size A	22 mm
ABBA Straps BCA28	ABBA	BCA28	47,-	Stock		Color A	Zwart
ABBA Straps BCA28	ABBA	BCA28	47,-	Stock		Color C	Rood
ABBA Straps BCA28	ABBA	BCA28	47,-	Stock		Lock	Gesp
ABBA Straps BCA28	ABBA	BCA28	47,-	Stock		Color B	Zilver
ABBA Straps BCA28	ABBA	BCA28	47,-	Stock		12U	75 mm
ABBA Straps BCA28	ABBA	BCA28	47,-	Stock		6U	120 mm
ABBA Straps BCA28	ABBA	BCA28	47,-	Stock		Orig	Nee
ABBA Straps BCA28	ABBA	BCA28	47,-	Stock		Start	Pushpins
ABBA Straps BCA28	ABBA	BCA28	47,-	Stock		Straight	Ja
ABBA Straps BCA301	ABBA	BCA301	30,-	Stock		Size	20 mm
ABBA Straps BCA301	ABBA	BCA301	30,-	Stock		Material	Metaal
ABBA Straps BCA301	ABBA	BCA301	30,-	Stock		Extra info	Stainless Steel Bracelet
ABBA Straps BCA301	ABBA	BCA301	30,-	Stock		Size	20 mm
ABBA Straps BCA301	ABBA	BCA301	30,-	Stock		Color A	Roségoud
ABBA Straps BCA301	ABBA	BCA301	30,-	Stock		Lock	VWS
ABBA Straps BCA301	ABBA	BCA301	30,-	Stock		Color B	Roségoud
ABBA Straps BCA301	ABBA	BCA301	30,-	Stock		Orig	Nee
ABBA Straps BCA301	ABBA	BCA301	30,-	Stock		Start	Pushpins
ABBA Straps BCA301	ABBA	BCA301	30,-	Stock		Straight	Nee
ABBA Straps BCA22	ABBA	BCA22	46,-	Stock		Size	22 mm
ABBA Straps BCA22	ABBA	BCA22	46,-	Stock		Material	Leer
ABBA Straps BCA22	ABBA	BCA22	46,-	Stock		Size	22 mm
ABBA Straps BCA22	ABBA	BCA22	46,-	Stock		Size A	22 mm
ABBA Straps BCA22	ABBA	BCA22	46,-	Stock		Size B	20 mm
ABBA Straps BCA22	ABBA	BCA22	46,-	Stock		Color A	Zwart
ABBA Straps BCA22	ABBA	BCA22	46,-	Stock		Color C	Wit
ABBA Straps BCA22	ABBA	BCA22	46,-	Stock		Lock	Gesp
ABBA Straps BCA22	ABBA	BCA22	46,-	Stock		Color B	Zilver
ABBA Straps BCA22	ABBA	BCA22	46,-	Stock		12U	75 mm
ABBA Straps BCA22	ABBA	BCA22	46,-	Stock		6U	120 mm
ABBA Straps BCA22	ABBA	BCA22	46,-	Stock		Orig	Nee
ABBA Straps BCA22	ABBA	BCA22	46,-	Stock		Start	Pushpins
ABBA Straps BCA22	ABBA	BCA22	46,-	Stock		Straight	Ja

Sheet 2:

Size

Material

Extra info

Size

Size A

Size B

Color A

Color C

Lock

Color B

12U

6U

Orig

Start

Straight

ABBA Straps BCA28

22 mm

Leer

22 mm

22 mm

Zwart

Rood

Gesp

Zilver

75 mm

120 mm

Nee

Pushpins

Ja

ABBA Straps BCA301

20 mm

Metaal

Stainless steel bracelet

20 mm

Roségoud

VWS

Roségoud

Nee

Pushpins

Nee

ABBA Straps BCA22

22 mm

Leer

22 mm

22 mm

20 mm

Zwart

Wit

Gesp

Zilver

75 mm

120 mm

Nee

Pushpins

Ja

 

[Kerryx]

What columns are listed in the previous step ,does it look like this?

 

[InfinityMrsn]

@Kerryx Yes, please see image

 

[Kerryx]

Remove column Aangepast and continue as below

Select all the columns basicalyl 2nd column to last , as you want to remove all values with "null" and simply replace values as follows

 

[InfinityMrsn]

@Kerryx How can I thank you. This works perfectly ! I'm so glad you helped me. This will save me thousands of hours.
Many Many thanks for your help.

 

[Kerryx]

Glad to help