Create new records from existing ones based on 'rule'?
 

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.

Create new records from existing ones based on 'rule'?
 

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" wrote in message
...
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.