combine data from multiple tables to show in a separate table in excel
I have a workbook that I need to combine data from multiple tables per a condition then add that data to a separate table for referencing.
My example has three tables where tables '8 and '9' are the tables that hold the data. I need to combine the data from these tables where the row number is greater than 11. The combined data needs to be shown in the 'Extras' table sequentially.
This is a part of a bigger solution I am building where all the data from specified tables is being put into a calendar and the calendar only has 10 slots for each time period throughout the day and I need to be able to round up all the extra data for these time slots and put it into a table to reference against.
The data in this solution will change from day to day so the data in each of the tables will change everyday. Also, the master sheets for this solution are connected to database tables so the actual data is being pulled from there not manually entered.
The size of all of the tables is A1:C200 so if each table is full of data the 'Extras' table could be very large.
I have been playing around with an index function but I have not been able to combine the data from '8' and '9' in one statement.
Any suggestions would be greatly appretiated. Please provide code examples with your reply.
Thank you
workbookSample.xlsx
My example has three tables where tables '8 and '9' are the tables that hold the data. I need to combine the data from these tables where the row number is greater than 11. The combined data needs to be shown in the 'Extras' table sequentially.
This is a part of a bigger solution I am building where all the data from specified tables is being put into a calendar and the calendar only has 10 slots for each time period throughout the day and I need to be able to round up all the extra data for these time slots and put it into a table to reference against.
The data in this solution will change from day to day so the data in each of the tables will change everyday. Also, the master sheets for this solution are connected to database tables so the actual data is being pulled from there not manually entered.
The size of all of the tables is A1:C200 so if each table is full of data the 'Extras' table could be very large.
I have been playing around with an index function but I have not been able to combine the data from '8' and '9' in one statement.
Any suggestions would be greatly appretiated. Please provide code examples with your reply.
Thank you
workbookSample.xlsx
ASKER
Yes, "9:0" is 9 am, it just isn't formatted. It doesn't need to be formatted. These two tables are automatically filled from the master tables per specific criteria and they are not sorted. They are loaded into the main calendar as they appear, they don't need to be sorted in any particular way. The calendar is filled per a specified date and all the sheets behind the calendar are loaded with the specified date when specified by the user. The values will not change here or be changed here, the values are loaded when the master tables are synced with the data base table.
The only criteria I need is if the table has more data than the calendar can handle, so I need the data from the 12th row and higher (if there is any). Depending on the day, there may or may not be more than 10 WO's in the time slots.
The only criteria I need is if the table has more data than the calendar can handle, so I need the data from the 12th row and higher (if there is any). Depending on the day, there may or may not be more than 10 WO's in the time slots.
Here is a routine that will copy the extra rows.
If you want to move the rows instead change "Selection.Copy" to "Selection.Cut"
Sub CopyExtras()
Dim i As Long
Dim n As Long
Dim oSheet As Worksheet
n = 2 ' First row of Extras
For Each oSheet In ActiveWorkbook.Sheets
oSheet.Select
If oSheet.Name <> "Extras" Then
For i = 12 To oSheet.Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row
If oSheet.Cells(i, 1).Text <> "" Then
oSheet.Range("A" & i & ":C" & i).Select
Selection.Copy
Sheets("Extras").Select
Range("A" & n).Select
ActiveSheet.Paste
n = n + 1
oSheet.Select
End If
Next i
End If
Next oSheet
End SubIf you want to move the rows instead change "Selection.Copy" to "Selection.Cut"
ASKER
Thanks,
I do not know how to implement  the code you posted.
Can you give me instructions on how to implement this?
I am fairly new to Excel programming so this is new for me.
I do not know how to implement  the code you posted.
Can you give me instructions on how to implement this?
I am fairly new to Excel programming so this is new for me.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Thanks!
What if I wanted to specify which tables to pull from?
My main project has tables that I don't want to pull from and it looks like the code looks at all other tables.
What if I wanted to specify which tables to pull from?
My main project has tables that I don't want to pull from and it looks like the code looks at all other tables.
You could have a list of tables or just add a rule for whether a table should be included or not.
How do you know which tables are included?
How do you know which tables are included?
ASKER
There are 11 tables that I need to pull from to fill the 'Extras' table.
They are: 'I&E', 'Morning', 'Any', '8', '9', '10', '11', '12', '1', '2', '3'
All the tables have 10 spots on the calendar except 'I&E' and 'Morning', they are different from the rest.
In the Calendar:
'I&E' has 22 spots instead of 10
'Morning' has 20 spots instead of 10
These two tables are the only exception where they may have extras but they have more available spots on the calendar than the rest of the time slots.
They are: 'I&E', 'Morning', 'Any', '8', '9', '10', '11', '12', '1', '2', '3'
All the tables have 10 spots on the calendar except 'I&E' and 'Morning', they are different from the rest.
In the Calendar:
'I&E' has 22 spots instead of 10
'Morning' has 20 spots instead of 10
These two tables are the only exception where they may have extras but they have more available spots on the calendar than the rest of the time slots.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Works great!
One more question:
Is there a way to make the macro run automatically when the project is opened?
One more question:
Is there a way to make the macro run automatically when the project is opened?
Yes, there is.
How does the data get into the spreadsheet? I'm guessing you will want the macro to be run when the data is imported rather than when the spreadsheet is opened.
How does the data get into the spreadsheet? I'm guessing you will want the macro to be run when the data is imported rather than when the spreadsheet is opened.
ASKER
That's correct.
The user has to log in to get the database data when the spreadsheet is opened. Once logged-in, then the data is gathered and the tables are filled.
The macro should run right after the tables are filled so it gets all the extras.
The user has to log in to get the database data when the spreadsheet is opened. Once logged-in, then the data is gathered and the tables are filled.
The macro should run right after the tables are filled so it gets all the extras.
Is the data gathering process automated or something that is done manually by the user? If you can provide a brief description of what the user does and how then I can offer a suggestion or two about how to invoke the macro.
By the way, it's not clear to me if you want to copy or move the data to Extras. As written, the macro copies it; there is a comment showing how to move it instead.
By the way, it's not clear to me if you want to copy or move the data to Extras. As written, the macro copies it; there is a comment showing how to move it instead.
ASKER
The user opens the project, then the user is prompted to log-in to the database connections to retrieve the updated data. There are four tables that require log-in at start-up. Once the user is logged in then the data is loaded automatically into all the tables and the calendar. The user won't have any more interaction with the project other than printing the calendar for their manager.
The macro needs to run after all the data is loaded from the database and the tables are filled.
Copying the data is fine. The macro does what I need it to do I just don't want to have to initialize it. I would like it to be automated into the project for simplicity for the user. I would rather not add a button to initialize the macro if I don't have to.
The macro needs to run after all the data is loaded from the database and the tables are filled.
Copying the data is fine. The macro does what I need it to do I just don't want to have to initialize it. I would like it to be automated into the project for simplicity for the user. I would rather not add a button to initialize the macro if I don't have to.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Thanks I will check it out and let you know how it goes.
ASKER
tdlewis
Thanks for all the help on this project!!!
Thanks for all the help on this project!!!
Are the date/time slots sorted in tables 8 and 9? That is, when the date or time changes can the solution just move onto the next slot looking for extras?