What's new

Need to replicate All rows in one tab by All rows in another tab in Excel

Fever

Active member
Staff member
Hello Excel Vlookup Experts,

I am trying to get a VLOOKUP formula to do the following. Replicate the number of records from the "sample" tab for as many items there are in the "options" tab. I believe this would be some type of distributive action achieved in Excel although I suppose it could be done using coding.

(A) Here is the initial "sample" data set. There are actually 41565 rows in the actual sample set but I have included the first 10 for illustration. enter image description here

(B) Here is the initial "options" data set.

enter image description here

(C) Here is a representation of what I'm looking for..for the full 41565 x 9 = 374085 records.
enter image description here

My concern also is that this approach is time consuming even if the formula is corrected to make it distribute correctly. I still need to manually drag down down the formulas manually for 374085 rows and then copy/paste values into another tab each time. I'm hoping there is a better solution either via VBScript or some other method either via Excel or perhaps coding outside of excel such as using JSON. That's an effort for another day.

In the past with Excel I have had some success in replicating smaller datasets where the number of records of in both tabs were the same. So far when it comes to replicating a set of sample records (i.e. 10 records) have used this formula below which allows for repeating 9 times for 90 repetitions of a given Email Address, Zipcode, City, State.

Email Address (begin at Cell B2 on Formulas tab)

=IF(ROWS(options!$A$1:$A$2)>COUNTA(options!$A$1:$A$9)*COUNTA(sample!A$2:$A$11),"",INDEX(sample!$B$2:$B$11,CEILING((ROW()-1)/(COUNTA(options!$A$1:$A$9)*COUNTA(sample!A$2:$A$11)),1)))


ZipCode (begin at Cell C2 on Formulas tab)

=IF(ROWS(options!$A$1:$A$2)>COUNTA(options!$A$1:$A$9)*COUNTA(sample!A$2:$A$11),"",INDEX(sample!$C$2:$C$11,CEILING((ROW()-1)/(COUNTA(options!$A$1:$A$9)*COUNTA(sample!A$2:$A$11)),1)))


City (begin at Cell D2 on Formulas tab)

=IF(ROWS(options!$A$1:$A$2)>COUNTA(options!$A$1:$A$9)*COUNTA(sample!A$2:$A$11),"",INDEX(sample!$D$2:$D$11,CEILING((ROW()-1)/(COUNTA(options!$A$1:$A$9)*COUNTA(sample!A$2:$A$11)),1)))


State (begin at Cell E2 on Formulas tab)

=IF(ROWS(options!$A$1:$A$2)>COUNTA(options!$A$1:$A$9)*COUNTA(sample!A$2:$A$11),"",INDEX(sample!$E$2:$E$11,CEILING((ROW()-1)/(COUNTA(options!$A$1:$A$9)*COUNTA(sample!A$2:$A$11)),1)))


For the 'Service Type' column, a different repetition is occurring. 10x9x10

When it comes to replicating a set of sample records (i.e. 10 records) I have used this formula below which allows for repeating the range accounting for up to 900 rows (10 rows in sample x 9 rows in options x 10 times need for items in options tab to multiple into sample rows). However, "as-is" it will display #REF! beyond 900 as it does not yet have the needed adjustment to say repeat the process it did for the first 900 again for the next 900 and again for as many rows that exists.

(begin at Cell F2 on Formulas tab)

=IF(ROWS(options!$A$1:$A$2)>COUNTA(options!$A$1:$A$9)*COUNTA(sample!A$2:$A$11),"",INDEX(options!$A$1:$A$9,CEILING((ROW()-1)/COUNTA(sample!A$2:$A$11),1)))


Can anyone provide at minimal a fix to the formula so it will work or better a more universal and streamlined approach to solving that work for data sets that will is more flexible in handling both small and large data sets where the 2 data set rows being joined don't have the same number of elements?

Continue reading...
 
Top