Merging multiple rows with the same preceding value into one row. 4-6k records.

[Mllam]

Hi Team,

I am working on taking the following information.

SKU	MOQ	Unit Price	Date Updated	Remark
123x	25​	5​	03-Jan-23​	Updated
123x	100​	3​	01-Nov-22​	Updated
123x	250​	2​	01-Nov-22​	Updated
321a	1000​	5​	02-Dec-21​	Needs Pricing Update
321a	5000​	4​	02-Dec-21​	Needs Pricing Update
321a	10000​	3​	02-Dec-21​	Needs Pricing Update
321a	50000​	2​	02-Dec-21​	Needs Pricing Update
321a	100000​	1​	03-Jan-23​	Updated

And turning it into the following. I have not added any formulas and am working through fixing my XL2BB for future posts.

SKU

MOQ

Unit Price

Volume 1

Price 1

Date Updated 1

Volume 2

Price 2

Date Updated 2

Volume 3

Price 3

Date Updated 3

Volume 4

Price 4

Date Updated 4

Volume 5

Price 5

Date Updated 5

Volume 6

Price 6

Date Updated 6

Remark

123x

25​

5​

25​

5​

03-Jan-23​

100​

3​

01-Nov-22​

250​

2​

01-Nov-22​

Updated

321a

1000​

5​

1000​

5​

02-Dec-21​

5000​

4​

02-Dec-21​

10000​

3​

02-Dec-21​

50000​

2​

02-Dec-21​

10000​

1​

03-Jan-23​

Needs Pricing Update, Updated

I appreciate your help on this one. I tried using pivot tables but got stuck multiple times trying to present it in this format.

Best,

Marco

 

[Zot]

Let helpers out there know if your SKU is always contiguous like in your sample or it can be non-contiguous.

 

[Legacy 143009]

For unique SKU list. In my example it is in G2:

=IFERROR(INDEX($A$2:$A$9,MATCH(0,INDEX(COUNTIF($G$1:G1,$A$2:$A$9),0,0),0)),"")

You may also use office 365's UNIQUE function to retrieve distinct SKU values.

Next to SKU number, you can paste this to H2:

=IFERROR(INDEX($B$2:$D$9,SMALL(IF($A$2:$A$9=$G2,ROW($A$1:$A$8),CEILING(COLUMNS($A$1:A1)/3,1)),MOD((COLUMN(A1)-1),3)+1),"")

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	SKU	MOQ	Unit Price	Date Updated	Remark			SKU	MOQ	Unit Price	Volume 1	Price 1	Date Updated 1	Volume 2	Price 2	Date Updated 2	Volume 3	Price 3	Date Updated 3	Volume 4	Price 4	Date Updated 4	Volume 5	Price 5	Date Updated 5	Volume 6	Price 6	Date Updated 6	Remark
2	123x	25	5	03-Jan-23	Updated			123x	25	5	25	5	03-Jan-23	100	3	01-Nov-22	250	2	01-Nov-22										Updated
3	123x	100	3	01-Nov-22	Updated			321a	1000	5	1000	5	02-Dec-21	5000	4	02-Dec-21	10000	3	02-Dec-21	50000	2	02-Dec-21	100000	1	03-Jan-23				Needs Pricing Update, Updated
4	123x	250	2	01-Nov-22	Updated
5	321a	1000	5	02-Dec-21	Needs Pricing Update
6	321a	5000	4	02-Dec-21	Needs Pricing Update
7	321a	10000	3	02-Dec-21	Needs Pricing Update
8	321a	50000	2	02-Dec-21	Needs Pricing Update
9	321a	100000	1	03-Jan-23	Updated
10
Data

Cell Formulas
Range	Formula
H2:H3	H2	=UNIQUE(FILTER(A2:A100,A2:A100<>""))
I2:J3	I2	=TAKE(FILTER($B$2:$C$100,$A$2:$A$100=H2),1)
K2:S2,K3:Y3	K2	=TOROW(FILTER($B$2:$D$100,$A$2:$A$100=H2))
AC2:AC3	AC2	=TEXTJOIN(", ",,UNIQUE(FILTER($E$2:$E$100,$A$2:$A$100=H2)))
Dynamic array formulas.