Prev: Concatenate with a specific number of spaces
Next: Address must be 18 characters long
From: msnyc07 on 27 May 2010 17:49
I am trying to pull apart records I uploaded that are set up something like
this;

MainField | Other Fields | AssociatedFIeld1 | AssociatedFieldN

There can be anywhere from zero to 6 Associated Fields. Trying to break a
new record apart for each e.g.

MainRecordX | Other Fields | WidgetA | WidgetB
MainRecordY | Other FIelds |WidgetC | Widget D | WidgetE
MainRecordZ | Other FIelds

should become

MainRecordX | Other Fields | WidgetA
MainRecordX | Other Fields | WidgetB
MainRecordY | Other Fields | WidgetC
MainRecordY | Other Fields | WidgetD
MainRecordY | Other Fields | WidgetE
MainRecordZ | Other Fields

Any insights appreciated.
From: Steve Dunn on 28 May 2010 09:38
Say your data is in Sheet1!A2:J50, with headers in A1:J1.

Columns B to D (for this example) contain your Other Fields, and E to J your
Associated Fields.

In Sheet2!A2:

=Sheet1!A2

copied along B2:E2.


In Sheet2!A3:

=IF(COUNTA(OFFSET(Sheet1!$E$2:$J$50,
MATCH(A2,Sheet1!$A$2:$A$50,0)-1,,1))>
COUNTIF(A$2:A2,A2),A2,INDEX(Sheet1!$A$2:$A$50,
MATCH(A2,Sheet1!$A$2:$A$50,0)+1))

In Sheet2!B3:

=INDEX(Sheet1!B$2:B$50,MATCH($A3,Sheet1!$A$2:$A$50,0))

copied along C3:D3.

In Sheet2!E3:

=INDEX(Sheet1!$E$2:$J$50,
MATCH($A3,Sheet1!$A$2:$A$50,0),
IF($A3=$A2,MATCH(E2,OFFSET(Sheet1!$E$2:$J$50,
MATCH($A3,Sheet1!$A$2:$A$50,0)-1,,1),0))+1)


Now copy A3:E3 down as far as required.

HTH
Steve D.


"msnyc07" <msnyc07(a)discussions.microsoft.com> wrote in message
news:893837B7-7E8D-40B9-A57E-357A4061E242(a)microsoft.com...
>I am trying to pull apart records I uploaded that are set up something like
> this;
>
> MainField | Other Fields | AssociatedFIeld1 | AssociatedFieldN
>
> There can be anywhere from zero to 6 Associated Fields. Trying to break a
> new record apart for each e.g.
>
> MainRecordX | Other Fields | WidgetA | WidgetB
> MainRecordY | Other FIelds |WidgetC | Widget D | WidgetE
> MainRecordZ | Other FIelds
>
> should become
>
> MainRecordX | Other Fields | WidgetA
> MainRecordX | Other Fields | WidgetB
> MainRecordY | Other Fields | WidgetC
> MainRecordY | Other Fields | WidgetD
> MainRecordY | Other Fields | WidgetE
> MainRecordZ | Other Fields
>
> Any insights appreciated.

 | 
Pages: 1
Prev: Concatenate with a specific number of spaces
Next: Address must be 18 characters long