Retrieve Data From Multiple Worksheets By Matching Column Headers And Row IDs

Hello Dear Excel Expert!

I got an vba code that does the followings
Loop through all worksheets in a workbook.
Copy Data in Sheet(1) By Matching Column Headers.

Here is the code

Please Login or Register  to view this content.

Background!
The data file that I got from databases administrator, is in MS EXCEL 2003 version.
The data is big in term of rows and columns so due to the number of columns limitation in Excel 2003.
Data with same Unique IDs are organized in next worksheets with the helper column "responseID" i.e. Column A in all worsheets.

For example;

If worksheet (1) has the data of ID "5" and it exceeds the number of column limitation (MS 2003) then its further data are placed to worksheets (2) if it still exceeds the column limitation then the data is placed to worksheet (3) and so on and it may goes from 1 to 50 worsheet(s)....but each worksheet has the uniqueID identifier in Column A i.e."responseID".

Process
What I do when i receive the file from database administrator,


I create new MS EXCEL WORKBOOK,as we work with MS EXCEL 2010 version (as there are extended number of columns 16384 I think, as compared to MS EXCEL 2003 that has only 255 Columns).

I copied all worksheets data into one worksheet by matching columns in it .Hectic Job!!!!

(I receive the file on daily basis with records ("ID") addition / deletion). This is another long process because as we receive any file, we start working on the data of this file, so newly received file will not have the changes incorporated that has been made to previous file......I dont want to describe this process here....I am working on this too in order to automate this section through any feasible way out...)

Goal

The above macro is not serving my purpose when it goes to next worksheets, it does not match the ID and place the data to its relevant row by matching ID,it only matching the column.

Do you people have any idea how this can be achieved!

Would be grateful to you people.

Blessings

Thank you!

Dear Leith Ross,

Thank you for your reply,

I have attached a file named "Match Rows and Columns.xlsm".

The File contains 6 worksheets;

Worksheet named "Combined", where the result should be appeared by matching row ID (in column "A" in all worksheets other than Combined worksheet) and column headers (Row 1 in all worksheets) of all worksheets in the file.
Worksheets named "1","2","3","4","5". are my source worksheets data of just one project.

Meanwhile I am getting the headers (ROW 1) manually in the worksheets "Combined" by copying the row 1 from all worksheets."1","2","3" and so on.

There is macro button placed on worksheet "Combined" please run it when get the macro finished look at the Column B you will find duplicate IDs in this column because macro is not matching the rows IDs it just matching Column headers...

Thanks once again for your interest.

Blessings!

Dear xladept,
Yes they dont line up the data of that specifc row IDs

Thanks!

Hi Leith Ross,

In all source worksheets, the column headers "responseid" located in Column "A" this is unique identifier,we identify our data with this "ID" it can never be duplicated.
However there is another header in column B named "respid" has the same data but we do not refer this as unique identifier.(we treat this just a data).
One more thing, in the worksheet ("Combined") macro generate serial number in Column A.

Is that clear ?

Thanks!

Dear Leith Ross,

I do not understand why did you mention "Which one should be copied to "Combined"?" what do you mean "should be copied" ?
The "Combined" worksheet will copy all the data by matching column if any header not available in the combined worksheet then macro should not copy that column of data from all source worksheets....

Did you get my point ?

Dear xladept,
I have mentioned about this in earlier thread, Yes "Serial By Macro" just consecutive numbers generated macro, we do not need match this column with,

We have to match column header "responseid" located in Column "A" in all source worksheets.

Does this clear ?

BUMP - need a reply!!!

Any suggestion!

Where are you getting puzzled ?....May be i help you!

xladept,

I would request you read my first thread! where you get stuck reference with this line of sentence..

Thank you!

A subsidiary worksheet’s (SOURCE WORKSHEETS) first column (COLUMN A) i.e. "responseID" must match to the column header already in place in the “combined” sheet (YES),


however the rows don’t line up (may we assume that they’re in order?) (All source worksheets responseID are always in ascending order). So each row’s column “A” (COLUMN A of ALL SOURCE WORKSHEETS) entry must match to the response-id in column 2 of the combined sheet (YES - COLUMN B OF COMBINED WORKSHEETS) (are the response-ids already present in column 2?) ("NOT THE MACRO PULLED IT THE MACRO I ATTACHED WITH THIS FILE")

So, is the “combined” sheet already set up with the column headers (YES I MANUALLY COPY FROM ALL SOURCE WORKSHEETS AND PASTE INTO COMBINED WORKSHET)and the response-ids (MACRO WILL PULL RESPONSE IDS IT IS NOT SET UP)?


I am attaching this file there is button in "Combined Worksheet" please run and you will observe duplicate/ triplicate responseID in Column B (responseID) in "Combined" Worksheet.
Match Rows and Columns.xlsm

Dear xladept,

Plaese ignore the file,Match Rows and Columns updated.xlsm.I have attached new amended file now the picture will get clear to you.

Please run the macro button placed on combined worksheets and tell me what you have noticed...

Hi xladept!

I ran this in my original data (just same as file i attached in my second thread which has massive data)
The macro took around 10-15 minutes to complete its session.after finished the result is not as expected...
I didn't even copy the data of sheet 2,3,4 and 5

Only copies the data of sheet "1"

xladept, in the last sample the code worked partially!


Because I have need to add column in my destination worksheet so i added some column in between then run the code it didn't copied the column of data after the column addition.

And we need to add a line of code to clearconents in destination worksheet because if the macro is run again then it also not working as it should be.

Thanks for your time xladept!

Yes I added the header for new columns.


Here is short process of my working;may be it help you understand the process.

I receive this big data file from DB administrator in MS EXCEL 2003 version.
I convert that file into MS 2010 version give the file name Master and then copy the data of all its worksheets into one worksheets by matching columns headers and row IDs
Then i add columns in between the existing columns to start my working on the file.


Next day i again receive the new file from DB administrator with addition of responseIDs.
I delete all those responseID from this newly received file,the resonseID which are already exist in my Master file and only copy the new responseID's data by matching column headers.

This big data file is the data of only one project. simultaneously there are 20-25 projects runs at a time
This exercise repeats until the project is finishes after the project finished then i send my master file to concerned department for their working.


Hope this helps!

Hi,
Any developement! i exhausted my search capabilities

Good Point!

Reason is the suppose we receive first file from DB Administrator contains in total 300 columns
And i have copied all the data into one master worksheets.
Second day we receive another file with addition of some column and responseIDs in betwen the data say 305 columns.
Then sequence of column orders with the source worksheets and destination worksheets will be different.

Got my point ?

Is there anyone who solve this grave issue ??

Oh xladept! thank you for your interest and your extended consistent efforts! Looking forward to your solution!

Hello xladept!
No luck! unexpected results came out.

I just have a quick test on my original data file twice/thrice in different way...
This code worked fine and the faster than previous one as well....To get thorough check i will have to give it a day...

There are two things if you can do easily....
Can we delete the line of code which is creating the first column serialization by macro in combined worksheets, we don't actually need it?

Secondly there are more worksheets will be exist in workbook since the macro is compiling the data from all worksheets other than combined so can we add line of code to exclude worksheets for which we do not want to pull data in combined worksheets ? like I have name of worksheets in the workbook...that need to be exclude..

"AddHeader"
"CodeList"
"AddColumns"
"Summary"
"Reports"
"CAPI" etc...



One more question i need to ask that can we rely on vba for such job to be done ?
Because the data set is too large and each and every time checking each columns and rows are not possble for me...

Hi xladept, in this part array will include those worksheets for which i want to pull data into combined worksheet right ?

Please Login or Register  to view this content.

Sicne coulple of days I am too busy in delivering the data of existing project therefore I could not get a chance to check it thorougly..but i will check and revert to you soon

AND THANK YOU SO MUCH FOR YOUR TIME AND EFFORTS.

REALLY APPRECIATING!

THANKS AGAIN AND AGAIN AND AGAIN!

Hi xladept!

if you remember that we have deleted the serial by macro serial numbers below the reference line;

Can we delete the line of code which is creating the first column serialization by macro in combined worksheets, we don't actually need it?
But we need macro to be start from column A - COLUMN A is my responseID.

Please Login or Register  to view this content.

Try the attached

Hi xladept!

I want to send you my original file how can i send you privately ?

I need to show you something that is going beyond to my head!

Dear xladept!
I have sent you the file please check!

thank u very much,i got the solution....,

Again Thank you xladept for you non-stop cooperation and contribution. However it will give it a check in the file.

And definitely chakravarthysm is thanking to you...

Hi Xladept,

In your file please add a column after COLUMN A between "responseID" and "respid" give any name to newly added column and then run macro, see what happens!

Hi xladept,
Let me check! will let you know on this very soon

Thanks again!

Hi,
I am getting error in this line, Don't know why

Please Login or Register  to view this content.

Hi xladept,
Thanks for your reply, I am not using any other routine but the following, I am getting Runtime error 9 "Script out of range".on the highlighted line. if you please check.

Please Login or Register  to view this content.

Hi xladept,

Hope you're doing great!

Due to the some issues i couldn't respond you on time.

Match Rows and Columns 18-04-14.xlsm
Please find attached file, i am still getting error on below line, however sometime it works but I am not getting desired outputs.

Please Login or Register  to view this content.

For ease, i have included a sheet named "Desired_Result" which may help you to understand it.

Looking forward to your usual cooperation!

Thanks so much.

Hi xladept!

Hope you're fine!

It is my good luck that I got you online.

I checked the file and ran the macro, after pressing the button, it removed all the headers & appended only IDs in "Combine" sheet.

Please note that we manually combine the headers in combine sheet & we do not need macro to do this automatically, there are some reasons.

But we need macro to look into the specific sheets (like sheet 1,2,3,4,5 as an example in the file) and compile / append all the data in combine sheet by looking at (matching) the columns.

Would highly appreciate if you please look into this.

THANKS VERY VERY MUCH FOR ALL YOUR EFFORTS!

Hi xladept,

Yes i used the same book that you have attached, I am attaching the same one,Ariff.xlsm just only click the button and see what happen.

Thanks!

xladept, did you face the same issue that i have faced ? when i click on the button it removes all headers & extract only response id.

Yes you can present it whomsoever you want to....


Thanks xladept for your continued cooperation