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



## Mllam (Jan 3, 2023)

Hi Team,

I am working on taking the following information.


SKUMOQUnit PriceDate UpdatedRemark123x25​5​03-Jan-23​Updated123x100​3​01-Nov-22​Updated123x250​2​01-Nov-22​Updated321a1000​5​02-Dec-21​Needs Pricing Update321a5000​4​02-Dec-21​Needs Pricing Update321a10000​3​02-Dec-21​Needs Pricing Update321a50000​2​02-Dec-21​Needs Pricing Update321a100000​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.


SKUMOQUnit PriceVolume 1Price 1Date Updated 1Volume 2Price 2Date Updated 2Volume 3Price 3Date Updated 3Volume 4Price 4Date Updated 4Volume 5Price 5Date Updated 5Volume 6Price 6Date Updated 6Remark123x25​5​25​5​03-Jan-23​100​3​01-Nov-22​250​2​01-Nov-22​Updated321a1000​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 (Jan 4, 2023)

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


----------



## Flashbond (Jan 4, 2023)

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 (Jan 4, 2023)

Another option
Fluff.xlsmABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC1SKUMOQUnit PriceDate UpdatedRemarkSKUMOQUnit PriceVolume 1Price 1Date Updated 1Volume 2Price 2Date Updated 2Volume 3Price 3Date Updated 3Volume 4Price 4Date Updated 4Volume 5Price 5Date Updated 5Volume 6Price 6Date Updated 6Remark2123x25503-Jan-23Updated123x25525503-Jan-23100301-Nov-22250201-Nov-22Updated3123x100301-Nov-22Updated321a100051000502-Dec-215000402-Dec-2110000302-Dec-2150000202-Dec-21100000103-Jan-23Needs Pricing Update, Updated4123x250201-Nov-22Updated5321a1000502-Dec-21Needs Pricing Update6321a5000402-Dec-21Needs Pricing Update7321a10000302-Dec-21Needs Pricing Update8321a50000202-Dec-21Needs Pricing Update9321a100000103-Jan-23Updated10DataCell FormulasRangeFormulaH2:H3H2=UNIQUE(FILTER(A2:A100,A2:A100<>""))I2:J3I2=TAKE(FILTER($B$2:$C$100,$A$2:$A$100=H2),1)K2:S2,K3:Y3K2=TOROW(FILTER($B$2:$D$100,$A$2:$A$100=H2))AC2:AC3AC2=TEXTJOIN(", ",,UNIQUE(FILTER($E$2:$E$100,$A$2:$A$100=H2)))Dynamic array formulas.


----------

