I have an Excel 2007 worksheet that is meant to process data that repeatedly
comes in garbled in a consistent way (i.e., what I call columns E-G always
import together correctly, and columns H-M always import together correctly,
but about 1/3 of the time, the E-G values in a given row correspond to a
different record than columns H-M in that same row, but I cannot control the
data source that generates this, so I need a way to process after the fact).

So I insert some other columns in columns B-D and have formulas there which
allow me to use the match function all the way down column A (this is from
cell A2, obviously):
=IF(C2<LEFT(D2,LEN(C2)),MATCH(D2,C$2:C$501,0)+1," match")
which generates the row number where the proper H-M values reside that go
with the E-G values in the row where this formula is.

My question is what formula (or macro, if need be) can I use to
auto-populate new rows that bring in the correct data from all columns E-M
(output can be on SHEET2 within the same file as SHEET1)? For example, if
the SHEET1!A2 formula indicates that the proper H-M values to go with E2:G2
are in H35:M35 then I need a way to put the value of =SHEET1!H35 in cell
SHEET2!H2, SHEET1!I35 in SHEET2!I2, etc., for all the mixed records.
ADVthanksANCE!

My apologies- I have just figured it out thanks to these Excel groups. I
kept seeing the INDIRECT function (which I didn't know before) used to answer
related questions, and figured out how to apply it to my situation. The
formula =INDIRECT("OrigData!$E"&$B2) populates my E (and similarly F-G)
value, and =INDIRECT("OrigData!$H"&ROW(A2)) populates my H (and similarly
I-M) value. Thanks!

"gutmach" wrote:

I have an Excel 2007 worksheet that is meant to process data that repeatedly
comes in garbled in a consistent way (i.e., what I call columns E-G always
import together correctly, and columns H-M always import together correctly,
but about 1/3 of the time, the E-G values in a given row correspond to a
different record than columns H-M in that same row, but I cannot control the
data source that generates this, so I need a way to process after the fact).

So I insert some other columns in columns B-D and have formulas there which
allow me to use the match function all the way down column A (this is from
cell A2, obviously):
=IF(C2<LEFT(D2,LEN(C2)),MATCH(D2,C$2:C$501,0)+1," match")
which generates the row number where the proper H-M values reside that go
with the E-G values in the row where this formula is.

My question is what formula (or macro, if need be) can I use to
auto-populate new rows that bring in the correct data from all columns E-M
(output can be on SHEET2 within the same file as SHEET1)? For example, if
the SHEET1!A2 formula indicates that the proper H-M values to go with E2:G2
are in H35:M35 then I need a way to put the value of =SHEET1!H35 in cell
SHEET2!H2, SHEET1!I35 in SHEET2!I2, etc., for all the mixed records.
ADVthanksANCE!