Index, row, columns and matching

[grimeyway]

• <article>
  I have some raw information, which has different information about the same product across a few rows, I need to take just one SKU for each product but then move some of the information that’s in the rows onto one row, so in the example I have the column marked market for each product, it is this info that needs to come onto one row. The number of rows for each sku will then be reduced, the products though the same are differentiated from each because of the SKU therefore I need to take each sku and then get the market info for it one one row, I have 10K lines to get through, please help. I have tried to be clear if I haven't please don't hesitate to ask any questions. Thanks in advance.
  
  sample posted below, showing the raw and what the final should hopefully look like.​
  </article>
  

https://onedrive.live.com/redir?resid=717CF2225B71C47D!584&authkey=!AAgMPgMv710NHZI&ithint=file,xlsx

 

[LloydFinancials]

Welcome Grimeyway,

You can try this:

Excel 2010
	A	F	S	T	U	V	W	X
1	Raw data
2	Sub-List	SKU	Market	Disc %
3	5046	12345	MDL Stock	61
4	5046	12345	Mass Market	55
5	5046	12345	Special Sales	61
6	5046	12345	HC Over One Pallet	61
7	5046	12345	Export	70
8	5046	56789	Trade	58
9	5046	56789	Mass Market	55
10	5046	56789	Special Sales	0
11	5046	56789	HC Over One Pallet	0
12	5046	56789	Export	0
13	5046	34567		0
14
15	Final data
16	Sub-List	SKU	Trade	MDL Stock	Mass Market	Special Sales	HC Over One Pallet	Export
17	5046	12345		61	55	61	61	70
18	5046	56789	58		55	0	0	0
19	5046	34567
Sheet1

Cell Formulas
Range	Formula
F4	=+IF(E3=E4,F3,0)
F9	=+IF(E8=E9,F8,0)
S17	=IFERROR(INDEX(OFFSET(INDIRECT(ADDRESS(MATCH($F17,$F$1:$F$13,0),19)),,,COUNTIF($F$3:$F$13,$F17),2),MATCH(S$16,OFFSET(INDIRECT(ADDRESS(MATCH($F17,$F$1:$F$13,0),19)),,,COUNTIF($F$3:$F$13,$F17),1),0),2),"")
T17	=IFERROR(INDEX(OFFSET(INDIRECT(ADDRESS(MATCH($F17,$F$1:$F$13,0),19)),,,COUNTIF($F$3:$F$13,$F17),2),MATCH(T$16,OFFSET(INDIRECT(ADDRESS(MATCH($F17,$F$1:$F$13,0),19)),,,COUNTIF($F$3:$F$13,$F17),1),0),2),"")
U17	=IFERROR(INDEX(OFFSET(INDIRECT(ADDRESS(MATCH($F17,$F$1:$F$13,0),19)),,,COUNTIF($F$3:$F$13,$F17),2),MATCH(U$16,OFFSET(INDIRECT(ADDRESS(MATCH($F17,$F$1:$F$13,0),19)),,,COUNTIF($F$3:$F$13,$F17),1),0),2),"")
V17	=IFERROR(INDEX(OFFSET(INDIRECT(ADDRESS(MATCH($F17,$F$1:$F$13,0),19)),,,COUNTIF($F$3:$F$13,$F17),2),MATCH(V$16,OFFSET(INDIRECT(ADDRESS(MATCH($F17,$F$1:$F$13,0),19)),,,COUNTIF($F$3:$F$13,$F17),1),0),2),"")
W17	=IFERROR(INDEX(OFFSET(INDIRECT(ADDRESS(MATCH($F17,$F$1:$F$13,0),19)),,,COUNTIF($F$3:$F$13,$F17),2),MATCH(W$16,OFFSET(INDIRECT(ADDRESS(MATCH($F17,$F$1:$F$13,0),19)),,,COUNTIF($F$3:$F$13,$F17),1),0),2),"")
X17	=IFERROR(INDEX(OFFSET(INDIRECT(ADDRESS(MATCH($F17,$F$1:$F$13,0),19)),,,COUNTIF($F$3:$F$13,$F17),2),MATCH(X$16,OFFSET(INDIRECT(ADDRESS(MATCH($F17,$F$1:$F$13,0),19)),,,COUNTIF($F$3:$F$13,$F17),1),0),2),"")

Best,

Luke