Need a macro to combine the data from sheets

  • Thread starter Thread starter showry
  • Start date Start date
S

showry

Hi All,

I have a 5 sheet workbook with different no.of columns(Max 20) & rows
(but max lines 1500) in each sheet but all sheets contains "A"row
unique code. that code should not repeat in all 5 sheets. I need a
combination of 5 sheets data into 6th sheet with accending codes.


for ex:


in Sheet1 the code contains 1,2,3,5,6,7,9 but in Sheet3 code
contains
4,10,12 etc. in sheet3 8,11,13 etc
but in sheet6 i need a codes and its info
1,2,3,4,5,6,7,8,9,10,11,12,13 so on..


Pls Help me.


Regards,
Anil.
 
Hi All,

I have a 5 sheet workbook with different no.of columns(Max 20) & rows
(but max lines 1500) in each sheet but all sheets contains "A"row
unique code. that code should not repeat in all 5 sheets. I need a
combination of 5 sheets data into 6th sheet with accending codes.

for ex:

in Sheet1 the code contains 1,2,3,5,6,7,9  but in Sheet3 code
contains
4,10,12 etc. in sheet3 8,11,13 etc
but in sheet6 i need a codes and its info
1,2,3,4,5,6,7,8,9,10,11,12,13 so on..

Pls Help me.

Regards,
Anil.
Click to expand...

I have tried to help you with this. you didn't respond to my last
email.
 
This might not be what you are looking for but, it is very simple.

Ex: (You can extrapolate to fit your needs)

Sheet1 A1 to A3 (1,2,4)
Sheet2 A1 to A3 (3,5,6)
Sheet3 A1 to A6 (Sheet1!A1, Sheet1!A2, Sheet1!A3, Sheet2!A1, Sheet2!
A2, Sheet2!A3)

Then, build your macro to re-sort Sheet3 Col A every time there is a
change to the values in Col A of Sheets 1 or 2.

This will do what you are asking, but it might not be a method you
want.

TK
 
If you don't have a solution yet, here's a brute-force way that
doesn't use a macro but seems to work, if I understand the problem.

In my example, Column A of Sheet1...Sheet5 contain the unique codes.
To help visualize things easier, I used 30 rows per sheet. You can
replace the 30's by 1500.

In Sheet6!A1 put
=IF(ISNUMBER(B1),RANK(B1,B:B,1),"")

In Sheet6!B1 put
=IF(INDIRECT(
"Sheet"&INT((ROW()-1)/30)+1&"!$A$"&(MOD(ROW()-1,30)+1))="","",
INDIRECT(
"Sheet"&INT((ROW()-1)/30)+1&"!$A$"&(MOD(ROW()-1,30)+1)))

In Sheet6!C1 put
=IF(ROW()>COUNT(B:B),"",VLOOKUP(ROW(),A:B,2,FALSE))

Select Sheet6!A1:C1 and copy down to row 150, or in the expanded case,
row 5*1500 or 7500.

The desired result is in Sheet6 column C. In there’s an #NA error in
column C, it means there's probably a repeated code. You can hide
columns A ands B.

Modify as needed.
 
Back
Top