Look up problem

**Perch** · 07-24-2009, 08:55 AM

hello

got a bit of a problem with some look up data I don't know how to solve . Basically I have rows of data that repeats every now and again and codes next to them
What I need to do is have same row of data ( without it repeating ) and relevant codes next to these across the page
Tried doing Pivot table and failed

Example and proposed format enclosed

please can anyone help ?

thank you

**DonkeyOte** · 07-24-2009, 09:06 AM

one way - using your sample file - and setup to avoid Arrays

D1: =SUMPRODUCT(1/COUNTIF($A$2:$A$100,$A$2:$A$100&""))
gives count of unique Sku's

C2: =COUNTIF($A:$A,$D2)
copied down as far as desired
gives count of Package Codes per Sku

D2: =IF(ROWS(D$2:D2)>$D$1,"",IF(ROWS(D$2:D2)=1,$A2,INDEX($A:$A,MATCH($D1,$A:$A,0)+COUNTIF($A:$A,$D1)+1)))
copied down as far as desired (ie inline with C)

E2: =IF(OR($D2="",COLUMNS($E2:E2)>$C2),"",INDEX($B:$B,MATCH($D2,$A:$A,0)+(COLUMNS($E2:E2)-1)))
copied both down and across for as many rows & columns as required

(the above per your file assumes that the data is grouped by Sku)

**Perch** · 07-24-2009, 09:45 AM

Donkey OTE

many thanks for quick reply - tried putting in formula but must be doing something wrong - list across the page should stop at red field ( enclosed )

am I not entering correctly ?

thanks
pete