[SOLVED] Formula to link a column of data between 2 worksheets and duplicate rows

Joe Braden · #1 01-27-2010, 10:04 AM

I'm in need of a formula, or another automated technique, to link a column of data from one worksheet to another. I have an input worksheet that I copy the raw data into that is formatted as follows:

COLUMN A
Row 1 12001
Row 2 12004
Row 3 12011
Row 4 12020
Row 5 13050
etc...

I need this import sheet to link to an output worksheet that would be updated automatically as new data is entered into the input sheet. However, I need the output sheet to copy the input values a specified # of the times and list them. For example, if the specified # was 3, it would be formatted as follows:

COLUMN A
Row 1 12001
Row 2 12001
Row 3 12001
Row 4 12004
Row 5 12004
Row 6 12004
Row 7 12011
etc...

I need the formula, or method, to be one that I don't have to continually re-enter as the data changes, as I have thousands of records to do this with. I've tried filling down, but autofill doesn't recognize an accurate trend. Is this something that I can accomplish by combining several fomulas, creating macros, or a combination of the both? Any help would be greatly appreciated.

NBVC · #2 01-27-2010, 10:33 AM

If you can start in Row 2 of Sheet2 (i.e.in Sheet2!A2) then:

and assuming raw data starts in A1 of Sheet1....

Code:

=IF(COUNTA($A$1:A1)>=COUNTA(Sheet1!A:A)*3,"",INDEX(Sheet1!A:A,MOD(INT((ROW(A1)-ROW($A$1))/3),COUNTA(Sheet1!A:A))+1))

Joe Braden · #3 01-29-2010, 09:50 AM

NBVC,

This is exactly what I needed.

Thx a ton for your help with this. I had reached out to numerous resources, and was unable to find a solution.

Welcome to the Excel Forum

Forum Statistics