If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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 C33. 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. |
Thread Tools | |
Display Modes | |
|
|