Consolidate data rows using 3 conditions - newbie to power query

N

newuser17

New Member
Joined
Sep 12, 2018
Messages
13
Dear Experts,

i have 30000 rows of data which consist of around 40 columns. the rows have duplicate data. what i require is consolidate these duplicate data using 3 conditions and insert a parent data row containing same data as the child row. the 3 conditions to validate are:

1. Code
2. SKU
3. Size

and also there are 2 columns which have MSRP and SP, when we consolidating the data we need to take the maximum value available in both columns seperatly

i have attached a screenshot of raw data and desired outcome here with. i have ignored the columns that are not related to above 3 validation. but those columns need to be available in the out put. (in the screenshot please refer category and description)
54168a2e4f6b5c15d26eb73718a79238-full.png


Is this doable in power query ? because this raw data process is going to be recurring very often.... or even with a macro is doable. thanks in advance for your effort and time..

i am using excel 2016 and with no 365 subscription on a win 7 platform
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
VBA Geek said:
Could post a sample of your data along with expected results
Click to expand...

this is will be my source data. i have more than 40 columns of data, but i have posted here only few which are related to the above 3 validations.

Excel 2016 (Windows) 32 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH][/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]H[/COLOR]​
[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​
[/TD]
[TD]Code[/TD]
[TD]SKU[/TD]
[TD]Category[/TD]
[TD]Descrption[/TD]
[TD]Size[/TD]
[TD]Stock[/TD]
[TD]MSRP[/TD]
[TD]SP[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​
[/TD]
[TD]OB581283[/TD]
[TD="bgcolor: #D9E1F2"]OB581283-BlackCombo[/TD]
[TD="bgcolor: #D9E1F2"]ABC[/TD]
[TD="bgcolor: #D9E1F2"]Oxford Embroidered Mini Dress[/TD]
[TD="bgcolor: #D9E1F2"]L[/TD]
[TD="bgcolor: #D9E1F2"]
1​
[/TD]
[TD="bgcolor: #D9E1F2"]
128​
[/TD]
[TD="bgcolor: #D9E1F2"]
35​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​
[/TD]
[TD]OB581283[/TD]
[TD="bgcolor: #D9E1F2"]OB581283-BlackCombo[/TD]
[TD="bgcolor: #D9E1F2"]ABC[/TD]
[TD="bgcolor: #D9E1F2"]Oxford Embroidered Mini Dress[/TD]
[TD="bgcolor: #D9E1F2"]L[/TD]
[TD="bgcolor: #D9E1F2"]
1​
[/TD]
[TD="bgcolor: #D9E1F2"]
128​
[/TD]
[TD="bgcolor: #D9E1F2"]
35​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​
[/TD]
[TD]OB581283[/TD]
[TD="bgcolor: #D9E1F2"]OB581283-BlackCombo[/TD]
[TD="bgcolor: #D9E1F2"]ABC[/TD]
[TD="bgcolor: #D9E1F2"]Oxford Embroidered Mini Dress[/TD]
[TD="bgcolor: #D9E1F2"]M[/TD]
[TD="bgcolor: #D9E1F2"]
1​
[/TD]
[TD="bgcolor: #D9E1F2"]
128​
[/TD]
[TD="bgcolor: #D9E1F2"]
35​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​
[/TD]
[TD]OB581283[/TD]
[TD="bgcolor: #D9E1F2"]OB581283-BlackCombo[/TD]
[TD="bgcolor: #D9E1F2"]ABC[/TD]
[TD="bgcolor: #D9E1F2"]Oxford Embroidered Mini Dress[/TD]
[TD="bgcolor: #D9E1F2"]M[/TD]
[TD="bgcolor: #D9E1F2"]
1​
[/TD]
[TD="bgcolor: #D9E1F2"]
128​
[/TD]
[TD="bgcolor: #D9E1F2"]
35​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​
[/TD]
[TD]OB581283[/TD]
[TD="bgcolor: #D9E1F2"]OB581283-BlackCombo[/TD]
[TD="bgcolor: #D9E1F2"]ABC[/TD]
[TD="bgcolor: #D9E1F2"]Oxford Embroidered Mini Dress[/TD]
[TD="bgcolor: #D9E1F2"]S[/TD]
[TD="bgcolor: #D9E1F2"]
1​
[/TD]
[TD="bgcolor: #D9E1F2"]
128​
[/TD]
[TD="bgcolor: #D9E1F2"]
35​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​
[/TD]
[TD]OB581283[/TD]
[TD="bgcolor: #D9E1F2"]OB581283-BlackCombo[/TD]
[TD="bgcolor: #D9E1F2"]ABC[/TD]
[TD="bgcolor: #D9E1F2"]Oxford Embroidered Mini Dress[/TD]
[TD="bgcolor: #D9E1F2"]XS[/TD]
[TD="bgcolor: #D9E1F2"]
1​
[/TD]
[TD="bgcolor: #D9E1F2"]
128​
[/TD]
[TD="bgcolor: #D9E1F2"]
35​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR]​
[/TD]
[TD]OB581283[/TD]
[TD="bgcolor: #D9E1F2"]OB581283-BlackCombo[/TD]
[TD="bgcolor: #D9E1F2"]ABC[/TD]
[TD="bgcolor: #D9E1F2"]Oxford Embroidered Mini Dress[/TD]
[TD="bgcolor: #D9E1F2"]XS[/TD]
[TD="bgcolor: #D9E1F2"]
1​
[/TD]
[TD="bgcolor: #D9E1F2"]
130​
[/TD]
[TD="bgcolor: #D9E1F2"]
35​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]11[/COLOR]​
[/TD]
[TD]OB581283[/TD]
[TD="bgcolor: #D9E1F2"]OB581283-BlackCombo[/TD]
[TD="bgcolor: #D9E1F2"]ABC[/TD]
[TD="bgcolor: #D9E1F2"]Oxford Embroidered Mini Dress[/TD]
[TD="bgcolor: #D9E1F2"]XS[/TD]
[TD="bgcolor: #D9E1F2"]
1​
[/TD]
[TD="bgcolor: #D9E1F2"]
128​
[/TD]
[TD="bgcolor: #D9E1F2"]
35​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]12[/COLOR]​
[/TD]
[TD]OB581283[/TD]
[TD="bgcolor: #D9E1F2"]OB581283-BlackCombo[/TD]
[TD="bgcolor: #D9E1F2"]ABC[/TD]
[TD="bgcolor: #D9E1F2"]Oxford Embroidered Mini Dress[/TD]
[TD="bgcolor: #D9E1F2"]XS[/TD]
[TD="bgcolor: #D9E1F2"]
1​
[/TD]
[TD="bgcolor: #D9E1F2"]
128​
[/TD]
[TD="bgcolor: #D9E1F2"]
35​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]13[/COLOR]​
[/TD]
[TD]OB581283[/TD]
[TD="bgcolor: #D9E1F2"]OB581283-BlackCombo[/TD]
[TD="bgcolor: #D9E1F2"]ABC[/TD]
[TD="bgcolor: #D9E1F2"]Oxford Embroidered Mini Dress[/TD]
[TD="bgcolor: #D9E1F2"]XS[/TD]
[TD="bgcolor: #D9E1F2"]
1​
[/TD]
[TD="bgcolor: #D9E1F2"]
128​
[/TD]
[TD="bgcolor: #D9E1F2"]
35​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]14[/COLOR]​
[/TD]
[TD]OB581283[/TD]
[TD="bgcolor: #C6E0B4"]OB581283-LilacCombo[/TD]
[TD="bgcolor: #C6E0B4"]ABC[/TD]
[TD="bgcolor: #C6E0B4"]Oxford Embroidered Mini Dress[/TD]
[TD="bgcolor: #C6E0B4"]L[/TD]
[TD="bgcolor: #C6E0B4"]
1​
[/TD]
[TD="bgcolor: #C6E0B4"]
128​
[/TD]
[TD="bgcolor: #C6E0B4"]
35​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]15[/COLOR]​
[/TD]
[TD]OB581283[/TD]
[TD="bgcolor: #C6E0B4"]OB581283-LilacCombo[/TD]
[TD="bgcolor: #C6E0B4"]ABC[/TD]
[TD="bgcolor: #C6E0B4"]Oxford Embroidered Mini Dress[/TD]
[TD="bgcolor: #C6E0B4"]M[/TD]
[TD="bgcolor: #C6E0B4"]
1​
[/TD]
[TD="bgcolor: #C6E0B4"]
128​
[/TD]
[TD="bgcolor: #C6E0B4"]
35​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]16[/COLOR]​
[/TD]
[TD]OB581283[/TD]
[TD="bgcolor: #C6E0B4"]OB581283-LilacCombo[/TD]
[TD="bgcolor: #C6E0B4"]ABC[/TD]
[TD="bgcolor: #C6E0B4"]Oxford Embroidered Mini Dress[/TD]
[TD="bgcolor: #C6E0B4"]S[/TD]
[TD="bgcolor: #C6E0B4"]
1​
[/TD]
[TD="bgcolor: #C6E0B4"]
128​
[/TD]
[TD="bgcolor: #C6E0B4"]
35​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]17[/COLOR]​
[/TD]
[TD]OB581283[/TD]
[TD="bgcolor: #C6E0B4"]OB581283-LilacCombo[/TD]
[TD="bgcolor: #C6E0B4"]ABC[/TD]
[TD="bgcolor: #C6E0B4"]Oxford Embroidered Mini Dress[/TD]
[TD="bgcolor: #C6E0B4"]S[/TD]
[TD="bgcolor: #C6E0B4"]
1​
[/TD]
[TD="bgcolor: #C6E0B4"]
128​
[/TD]
[TD="bgcolor: #C6E0B4"]
35​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]18[/COLOR]​
[/TD]
[TD]OB581283[/TD]
[TD="bgcolor: #C6E0B4"]OB581283-LilacCombo[/TD]
[TD="bgcolor: #C6E0B4"]ABC[/TD]
[TD="bgcolor: #C6E0B4"]Oxford Embroidered Mini Dress[/TD]
[TD="bgcolor: #C6E0B4"]S[/TD]
[TD="bgcolor: #C6E0B4"]
1​
[/TD]
[TD="bgcolor: #C6E0B4"]
126​
[/TD]
[TD="bgcolor: #C6E0B4"]
35​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]19[/COLOR]​
[/TD]
[TD]OB581283[/TD]
[TD="bgcolor: #C6E0B4"]OB581283-LilacCombo[/TD]
[TD="bgcolor: #C6E0B4"]ABC[/TD]
[TD="bgcolor: #C6E0B4"]Oxford Embroidered Mini Dress[/TD]
[TD="bgcolor: #C6E0B4"]S[/TD]
[TD="bgcolor: #C6E0B4"]
1​
[/TD]
[TD="bgcolor: #C6E0B4"]
128​
[/TD]
[TD="bgcolor: #C6E0B4"]
35​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]20[/COLOR]​
[/TD]
[TD]OB581283[/TD]
[TD="bgcolor: #C6E0B4"]OB581283-LilacCombo[/TD]
[TD="bgcolor: #C6E0B4"]ABC[/TD]
[TD="bgcolor: #C6E0B4"]Oxford Embroidered Mini Dress[/TD]
[TD="bgcolor: #C6E0B4"]XS[/TD]
[TD="bgcolor: #C6E0B4"]
1​
[/TD]
[TD="bgcolor: #C6E0B4"]
128​
[/TD]
[TD="bgcolor: #C6E0B4"]
35​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]21[/COLOR]​
[/TD]
[TD]OB581283[/TD]
[TD="bgcolor: #C6E0B4"]OB581283-LilacCombo[/TD]
[TD="bgcolor: #C6E0B4"]ABC[/TD]
[TD="bgcolor: #C6E0B4"]Oxford Embroidered Mini Dress[/TD]
[TD="bgcolor: #C6E0B4"]XS[/TD]
[TD="bgcolor: #C6E0B4"]
1​
[/TD]
[TD="bgcolor: #C6E0B4"]
128​
[/TD]
[TD="bgcolor: #C6E0B4"]
34​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]22[/COLOR]​
[/TD]
[TD]OB581283[/TD]
[TD="bgcolor: #C6E0B4"]OB581283-LilacCombo[/TD]
[TD="bgcolor: #C6E0B4"]ABC[/TD]
[TD="bgcolor: #C6E0B4"]Oxford Embroidered Mini Dress[/TD]
[TD="bgcolor: #C6E0B4"]XS[/TD]
[TD="bgcolor: #C6E0B4"]
1​
[/TD]
[TD="bgcolor: #C6E0B4"]
128​
[/TD]
[TD="bgcolor: #C6E0B4"]
35​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]23[/COLOR]​
[/TD]
[TD]OB581283[/TD]
[TD="bgcolor: #DBDBDB"]OB581283-NeutralCombo[/TD]
[TD="bgcolor: #DBDBDB"]ABC[/TD]
[TD="bgcolor: #DBDBDB"]Oxford Embroidered Mini Dress[/TD]
[TD="bgcolor: #DBDBDB"]L[/TD]
[TD="bgcolor: #DBDBDB"]
1​
[/TD]
[TD="bgcolor: #DBDBDB"]
128​
[/TD]
[TD="bgcolor: #DBDBDB"]
35​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]24[/COLOR]​
[/TD]
[TD]OB581283[/TD]
[TD="bgcolor: #DBDBDB"]OB581283-NeutralCombo[/TD]
[TD="bgcolor: #DBDBDB"]ABC[/TD]
[TD="bgcolor: #DBDBDB"]Oxford Embroidered Mini Dress[/TD]
[TD="bgcolor: #DBDBDB"]L[/TD]
[TD="bgcolor: #DBDBDB"]
1​
[/TD]
[TD="bgcolor: #DBDBDB"]
128​
[/TD]
[TD="bgcolor: #DBDBDB"]
35​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]25[/COLOR]​
[/TD]
[TD]OB581283[/TD]
[TD="bgcolor: #DBDBDB"]OB581283-NeutralCombo[/TD]
[TD="bgcolor: #DBDBDB"]ABC[/TD]
[TD="bgcolor: #DBDBDB"]Oxford Embroidered Mini Dress[/TD]
[TD="bgcolor: #DBDBDB"]M[/TD]
[TD="bgcolor: #DBDBDB"]
1​
[/TD]
[TD="bgcolor: #DBDBDB"]
128​
[/TD]
[TD="bgcolor: #DBDBDB"]
35​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]26[/COLOR]​
[/TD]
[TD]OB581283[/TD]
[TD="bgcolor: #DBDBDB"]OB581283-NeutralCombo[/TD]
[TD="bgcolor: #DBDBDB"]ABC[/TD]
[TD="bgcolor: #DBDBDB"]Oxford Embroidered Mini Dress[/TD]
[TD="bgcolor: #DBDBDB"]M[/TD]
[TD="bgcolor: #DBDBDB"]
1​
[/TD]
[TD="bgcolor: #DBDBDB"]
128​
[/TD]
[TD="bgcolor: #DBDBDB"]
37​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]27[/COLOR]​
[/TD]
[TD]OB581283[/TD]
[TD="bgcolor: #DBDBDB"]OB581283-NeutralCombo[/TD]
[TD="bgcolor: #DBDBDB"]ABC[/TD]
[TD="bgcolor: #DBDBDB"]Oxford Embroidered Mini Dress[/TD]
[TD="bgcolor: #DBDBDB"]M[/TD]
[TD="bgcolor: #DBDBDB"]
1​
[/TD]
[TD="bgcolor: #DBDBDB"]
128​
[/TD]
[TD="bgcolor: #DBDBDB"]
35​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]28[/COLOR]​
[/TD]
[TD]OB581283[/TD]
[TD="bgcolor: #DBDBDB"]OB581283-NeutralCombo[/TD]
[TD="bgcolor: #DBDBDB"]ABC[/TD]
[TD="bgcolor: #DBDBDB"]Oxford Embroidered Mini Dress[/TD]
[TD="bgcolor: #DBDBDB"]M[/TD]
[TD="bgcolor: #DBDBDB"]
1​
[/TD]
[TD="bgcolor: #DBDBDB"]
128​
[/TD]
[TD="bgcolor: #DBDBDB"]
35​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]29[/COLOR]​
[/TD]
[TD]OB581283[/TD]
[TD="bgcolor: #DBDBDB"]OB581283-NeutralCombo[/TD]
[TD="bgcolor: #DBDBDB"]ABC[/TD]
[TD="bgcolor: #DBDBDB"]Oxford Embroidered Mini Dress[/TD]
[TD="bgcolor: #DBDBDB"]M[/TD]
[TD="bgcolor: #DBDBDB"]
1​
[/TD]
[TD="bgcolor: #DBDBDB"]
128​
[/TD]
[TD="bgcolor: #DBDBDB"]
35​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]30[/COLOR]​
[/TD]
[TD]OB581283[/TD]
[TD="bgcolor: #DBDBDB"]OB581283-NeutralCombo[/TD]
[TD="bgcolor: #DBDBDB"]ABC[/TD]
[TD="bgcolor: #DBDBDB"]Oxford Embroidered Mini Dress[/TD]
[TD="bgcolor: #DBDBDB"]S[/TD]
[TD="bgcolor: #DBDBDB"]
1​
[/TD]
[TD="bgcolor: #DBDBDB"]
128​
[/TD]
[TD="bgcolor: #DBDBDB"]
35​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]31[/COLOR]​
[/TD]
[TD]OB581283[/TD]
[TD="bgcolor: #DBDBDB"]OB581283-NeutralCombo[/TD]
[TD="bgcolor: #DBDBDB"]ABC[/TD]
[TD="bgcolor: #DBDBDB"]Oxford Embroidered Mini Dress[/TD]
[TD="bgcolor: #DBDBDB"]S[/TD]
[TD="bgcolor: #DBDBDB"]
1​
[/TD]
[TD="bgcolor: #DBDBDB"]
128​
[/TD]
[TD="bgcolor: #DBDBDB"]
35​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]32[/COLOR]​
[/TD]
[TD]OB581283[/TD]
[TD="bgcolor: #DBDBDB"]OB581283-NeutralCombo[/TD]
[TD="bgcolor: #DBDBDB"]ABC[/TD]
[TD="bgcolor: #DBDBDB"]Oxford Embroidered Mini Dress[/TD]
[TD="bgcolor: #DBDBDB"]S[/TD]
[TD="bgcolor: #DBDBDB"]
1​
[/TD]
[TD="bgcolor: #DBDBDB"]
128​
[/TD]
[TD="bgcolor: #DBDBDB"]
35​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]33[/COLOR]​
[/TD]
[TD]OB581283[/TD]
[TD="bgcolor: #DBDBDB"]OB581283-NeutralCombo[/TD]
[TD="bgcolor: #DBDBDB"]ABC[/TD]
[TD="bgcolor: #DBDBDB"]Oxford Embroidered Mini Dress[/TD]
[TD="bgcolor: #DBDBDB"]XS[/TD]
[TD="bgcolor: #DBDBDB"]
1​
[/TD]
[TD="bgcolor: #DBDBDB"]
128​
[/TD]
[TD="bgcolor: #DBDBDB"]
35​
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet1[/TD]
[/TR]
</tbody>[/TABLE]

and this will be my desired output.

Excel 2016 (Windows) 32 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH][/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]L[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]M[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]N[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]O[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]P[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Q[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]R[/COLOR]​
[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​
[/TD]
[TD]SKU[/TD]
[TD]Category[/TD]
[TD]Descrption[/TD]
[TD]Size[/TD]
[TD]Stock[/TD]
[TD]MSRP[/TD]
[TD]SP[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​
[/TD]
[TD]OB581283[/TD]
[TD]ABC[/TD]
[TD]Oxford Embroidered Mini Dress[/TD]
[TD][/TD]
[TD][/TD]
[TD]
130​
[/TD]
[TD]
37​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​
[/TD]
[TD="bgcolor: #E2EFDA"]OB581283-BlackCombo[/TD]
[TD="bgcolor: #E2EFDA"]ABC[/TD]
[TD="bgcolor: #E2EFDA"]Oxford Embroidered Mini Dress[/TD]
[TD="bgcolor: #E2EFDA"]L[/TD]
[TD="bgcolor: #E2EFDA"]
2​
[/TD]
[TD="bgcolor: #E2EFDA"]
130​
[/TD]
[TD="bgcolor: #E2EFDA"]
37​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​
[/TD]
[TD="bgcolor: #E2EFDA"]OB581283-BlackCombo[/TD]
[TD="bgcolor: #E2EFDA"]ABC[/TD]
[TD="bgcolor: #E2EFDA"]Oxford Embroidered Mini Dress[/TD]
[TD="bgcolor: #E2EFDA"]M[/TD]
[TD="bgcolor: #E2EFDA"]
2​
[/TD]
[TD="bgcolor: #E2EFDA"]
130​
[/TD]
[TD="bgcolor: #E2EFDA"]
37​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​
[/TD]
[TD="bgcolor: #E2EFDA"]OB581283-BlackCombo[/TD]
[TD="bgcolor: #E2EFDA"]ABC[/TD]
[TD="bgcolor: #E2EFDA"]Oxford Embroidered Mini Dress[/TD]
[TD="bgcolor: #E2EFDA"]S[/TD]
[TD="bgcolor: #E2EFDA"]
1​
[/TD]
[TD="bgcolor: #E2EFDA"]
130​
[/TD]
[TD="bgcolor: #E2EFDA"]
37​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​
[/TD]
[TD="bgcolor: #E2EFDA"]OB581283-BlackCombo[/TD]
[TD="bgcolor: #E2EFDA"]ABC[/TD]
[TD="bgcolor: #E2EFDA"]Oxford Embroidered Mini Dress[/TD]
[TD="bgcolor: #E2EFDA"]XS[/TD]
[TD="bgcolor: #E2EFDA"]
5​
[/TD]
[TD="bgcolor: #E2EFDA"]
130​
[/TD]
[TD="bgcolor: #E2EFDA"]
37​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​
[/TD]
[TD="bgcolor: #FFF2CC"]OB581283-LilacCombo[/TD]
[TD="bgcolor: #FFF2CC"]ABC[/TD]
[TD="bgcolor: #FFF2CC"]Oxford Embroidered Mini Dress[/TD]
[TD="bgcolor: #FFF2CC"]L[/TD]
[TD="bgcolor: #FFF2CC"]
1​
[/TD]
[TD="bgcolor: #FFF2CC"]
130​
[/TD]
[TD="bgcolor: #FFF2CC"]
37​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR]​
[/TD]
[TD="bgcolor: #FFF2CC"]OB581283-LilacCombo[/TD]
[TD="bgcolor: #FFF2CC"]ABC[/TD]
[TD="bgcolor: #FFF2CC"]Oxford Embroidered Mini Dress[/TD]
[TD="bgcolor: #FFF2CC"]M[/TD]
[TD="bgcolor: #FFF2CC"]
1​
[/TD]
[TD="bgcolor: #FFF2CC"]
130​
[/TD]
[TD="bgcolor: #FFF2CC"]
37​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]11[/COLOR]​
[/TD]
[TD="bgcolor: #FFF2CC"]OB581283-LilacCombo[/TD]
[TD="bgcolor: #FFF2CC"]ABC[/TD]
[TD="bgcolor: #FFF2CC"]Oxford Embroidered Mini Dress[/TD]
[TD="bgcolor: #FFF2CC"]S[/TD]
[TD="bgcolor: #FFF2CC"]
4​
[/TD]
[TD="bgcolor: #FFF2CC"]
130​
[/TD]
[TD="bgcolor: #FFF2CC"]
37​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]12[/COLOR]​
[/TD]
[TD="bgcolor: #FFF2CC"]OB581283-LilacCombo[/TD]
[TD="bgcolor: #FFF2CC"]ABC[/TD]
[TD="bgcolor: #FFF2CC"]Oxford Embroidered Mini Dress[/TD]
[TD="bgcolor: #FFF2CC"]XS[/TD]
[TD="bgcolor: #FFF2CC"]
3​
[/TD]
[TD="bgcolor: #FFF2CC"]
130​
[/TD]
[TD="bgcolor: #FFF2CC"]
37​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]13[/COLOR]​
[/TD]
[TD="bgcolor: #EDEDED"]OB581283-NeutralCombo[/TD]
[TD="bgcolor: #EDEDED"]ABC[/TD]
[TD="bgcolor: #EDEDED"]Oxford Embroidered Mini Dress[/TD]
[TD="bgcolor: #EDEDED"]L[/TD]
[TD="bgcolor: #EDEDED"]
2​
[/TD]
[TD="bgcolor: #EDEDED"]
130​
[/TD]
[TD="bgcolor: #EDEDED"]
37​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]14[/COLOR]​
[/TD]
[TD="bgcolor: #EDEDED"]OB581283-NeutralCombo[/TD]
[TD="bgcolor: #EDEDED"]ABC[/TD]
[TD="bgcolor: #EDEDED"]Oxford Embroidered Mini Dress[/TD]
[TD="bgcolor: #EDEDED"]M[/TD]
[TD="bgcolor: #EDEDED"]
5​
[/TD]
[TD="bgcolor: #EDEDED"]
130​
[/TD]
[TD="bgcolor: #EDEDED"]
37​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]15[/COLOR]​
[/TD]
[TD="bgcolor: #EDEDED"]OB581283-NeutralCombo[/TD]
[TD="bgcolor: #EDEDED"]ABC[/TD]
[TD="bgcolor: #EDEDED"]Oxford Embroidered Mini Dress[/TD]
[TD="bgcolor: #EDEDED"]S[/TD]
[TD="bgcolor: #EDEDED"]
3​
[/TD]
[TD="bgcolor: #EDEDED"]
130​
[/TD]
[TD="bgcolor: #EDEDED"]
37​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]16[/COLOR]​
[/TD]
[TD="bgcolor: #EDEDED"]OB581283-NeutralCombo[/TD]
[TD="bgcolor: #EDEDED"]ABC[/TD]
[TD="bgcolor: #EDEDED"]Oxford Embroidered Mini Dress[/TD]
[TD="bgcolor: #EDEDED"]XS[/TD]
[TD="bgcolor: #EDEDED"]
1​
[/TD]
[TD="bgcolor: #EDEDED"]
130​
[/TD]
[TD="bgcolor: #EDEDED"]
37​
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet1[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
VBA Geek said:
as far as I can see you would need to make use of the Group By, read: https://www.powerquery.training/portfolio/group-summarize-data/
Click to expand...

thank you for the link. it is really an awesome article.

yes i agree, i need to use the group by.. but there is an additional step that is new row to added in top of the main grouping and copied part of the relevant date. you can see it in the first row of the desired output table.

the main groping is based on Code
sub groping based on the SKU
Sub Sub grouping by Size

so the parent row contains the part of data that are common to its child group. what i am trying to achieve here is group my all the child data and create a parent row in top of the each group. so its not just about grouping only. also i need to take the maximum of MSRP and SP if there is different figures.

i hope you understand my requirement. your further advise/ help much appreciated.
 
Upvote 0
your result table is a little "weird", how you got 130 in whole column MSRP and 37 in whole column SP?
 
Upvote 0
sandy666 said:
your result table is a little "weird", how you got 130 in whole column MSRP and 37 in whole column SP?
Click to expand...

yeah that is the case, i need find the maximum of those two rows and replace with lower value. for eg: for the same Code if there are two many data which is different from one to another we need take the maximum of the value. like 128 and 130 we need take the maximum, same applies to the SP also.
 
Upvote 0
try duplicate source table twice then
for the first duplicate:
Code: 
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Code", "SKU", "Category", "Descrption", "Size"}, {{"Stock", each Table.RowCount(_), type number}})
in
    #"Grouped Rows"[/SIZE]
for the second duplicate:
Code: 
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Code"}, {{"MSRP", each List.Max([MSRP]), type number}, {"SP", each List.Max([SP]), type number}})
in
    #"Grouped Rows"[/SIZE]
then:
Code: 
[SIZE=1]let
    Source = Table.NestedJoin(#"Table1 (2)",{"Code"},#"Table1 (3)",{"Code"},"Table1 (3)",JoinKind.LeftOuter),
    #"Expanded Table1 (3)" = Table.ExpandTableColumn(Source, "Table1 (3)", {"MSRP", "SP"}, {"MSRP", "SP"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table1 (3)",{"Code"})
in
    #"Removed Columns"[/SIZE]

with the result:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]SKU[/td][td=bgcolor:#70AD47]Category[/td][td=bgcolor:#70AD47]Descrption[/td][td=bgcolor:#70AD47]Size[/td][td=bgcolor:#70AD47]Stock[/td][td=bgcolor:#70AD47]MSRP[/td][td=bgcolor:#70AD47]SP[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]OB581283-BlackCombo[/td][td=bgcolor:#E2EFDA]ABC[/td][td=bgcolor:#E2EFDA]Oxford Embroidered Mini Dress[/td][td=bgcolor:#E2EFDA]L[/td][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#E2EFDA]
130​
[/td][td=bgcolor:#E2EFDA]
37​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]OB581283-BlackCombo[/td][td]ABC[/td][td]Oxford Embroidered Mini Dress[/td][td]M[/td][td]
2​
[/td][td]
130​
[/td][td]
37​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]OB581283-BlackCombo[/td][td=bgcolor:#E2EFDA]ABC[/td][td=bgcolor:#E2EFDA]Oxford Embroidered Mini Dress[/td][td=bgcolor:#E2EFDA]S[/td][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]
130​
[/td][td=bgcolor:#E2EFDA]
37​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]OB581283-BlackCombo[/td][td]ABC[/td][td]Oxford Embroidered Mini Dress[/td][td]XS[/td][td]
5​
[/td][td]
130​
[/td][td]
37​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]OB581283-LilacCombo[/td][td=bgcolor:#E2EFDA]ABC[/td][td=bgcolor:#E2EFDA]Oxford Embroidered Mini Dress[/td][td=bgcolor:#E2EFDA]L[/td][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]
130​
[/td][td=bgcolor:#E2EFDA]
37​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]OB581283-LilacCombo[/td][td]ABC[/td][td]Oxford Embroidered Mini Dress[/td][td]M[/td][td]
1​
[/td][td]
130​
[/td][td]
37​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]OB581283-LilacCombo[/td][td=bgcolor:#E2EFDA]ABC[/td][td=bgcolor:#E2EFDA]Oxford Embroidered Mini Dress[/td][td=bgcolor:#E2EFDA]S[/td][td=bgcolor:#E2EFDA]
4​
[/td][td=bgcolor:#E2EFDA]
130​
[/td][td=bgcolor:#E2EFDA]
37​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]OB581283-LilacCombo[/td][td]ABC[/td][td]Oxford Embroidered Mini Dress[/td][td]XS[/td][td]
3​
[/td][td]
130​
[/td][td]
37​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]OB581283-NeutralCombo[/td][td=bgcolor:#E2EFDA]ABC[/td][td=bgcolor:#E2EFDA]Oxford Embroidered Mini Dress[/td][td=bgcolor:#E2EFDA]L[/td][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#E2EFDA]
130​
[/td][td=bgcolor:#E2EFDA]
37​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]OB581283-NeutralCombo[/td][td]ABC[/td][td]Oxford Embroidered Mini Dress[/td][td]M[/td][td]
5​
[/td][td]
130​
[/td][td]
37​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]OB581283-NeutralCombo[/td][td=bgcolor:#E2EFDA]ABC[/td][td=bgcolor:#E2EFDA]Oxford Embroidered Mini Dress[/td][td=bgcolor:#E2EFDA]S[/td][td=bgcolor:#E2EFDA]
3​
[/td][td=bgcolor:#E2EFDA]
130​
[/td][td=bgcolor:#E2EFDA]
37​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]OB581283-NeutralCombo[/td][td]ABC[/td][td]Oxford Embroidered Mini Dress[/td][td]XS[/td][td]
1​
[/td][td]
130​
[/td][td]
37​
[/td][/tr]
[/table]
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
Power Query Data types and excel time format issue
Replies
5
Views
372
Anand Sydney
A
K
  • Question Question
Power query - stopping refresh if no data available
Replies
2
Views
225
Kariba
K
P
  • Question Question
Power Query to Merge and Fill in gaps in data
Replies
7
Views
344
alex78
A
C
  • Solved
Help me Convert, Merge and Transpose a pdf table into a singular row, and then repeat that step ad nauseam to create a database
Replies
5
Views
575
Clonk92
C
T
  • Question Question
Convert Inconsistent, Paragraph-Like Data to Columns & Rows for Import
Replies
5
Views
361
AlienSx
A

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

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