Copy and Paste Multiple sheet data in one sheet Formula

V

vishu

Board Regular
Joined
Oct 26, 2011
Messages
70
Office Version
  1. 2016
Platform
  1. Windows
Hello friends,
Please help me... I have 1 All_in_one sheet and 28 vendor sheets each sheet A Column is date column start from A3:A and G is Daily Amount column starting G3:G
Now want to copy and paste all vendor sheets data in 1 (All in one) sheet
e.g A is Date and Daily Amount column is each vendor (B, C, D, E, F and ....)

Please help
Below am showing one of the vendors data for reference

ABCDEFG
HelperDateItemQtyRateAmtDaily Amt
01-Apr-2401-Apr-24Chicken2.7210567
01-Apr-24Egg3140420
01-Apr-24L Bonless22805601547
02-Apr-2402-Apr-24Chicken3.1220682
02-Apr-24L Bonless22905801262
03-Apr-2403-Apr-24B Bonless3280840
03-Apr-24Egg31404201260
04-Apr-2404-Apr-24Chicken5.32401272
04-Apr-24Tandoor62001200
04-Apr-24L Bonless22905803052
05-Apr-2405-Apr-24Lollypop2260520520
06-Apr-2406-Apr-24Chicken5.12401224
06-Apr-24Lollypop2250500
06-Apr-24L Bonless2300600
06-Apr-24B Bonless42901160
06-Apr-24Tandoor1021021005584
07-Apr-2407-Apr-24Chicken62501500
07-Apr-24L Bonless23406802180
08-Apr-2408-Apr-24Chicken2.75250687.5
08-Apr-24B Bonless43001200
08-Apr-24L Bonless2.53408502737.5
09-Apr-2409-Apr-24Chicken2.7250675
09-Apr-24Tandoor522011001775
10-Apr-2410-Apr-24L Bonless2.2320704
10-Apr-24Egg31404201124
11-Apr-2411-Apr-24Chicken3.4250850
11-Apr-24Tandoor3220660
11-Apr-24B Bonless13003001810
12-Apr-2412-Apr-24L Bonless43201280
12-Apr-24Chicken52501250
12-Apr-24Tandoor2210420
12-Apr-24B Bonless13103103260
13-Apr-2413-Apr-24
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
vishu said:
Hello friends,
Please help me... I have 1 All_in_one sheet and 28 vendor sheets each sheet A Column is date column start from A3:A and G is Daily Amount column starting G3:G
Now want to copy and paste all vendor sheets data in 1 (All in one) sheet
e.g A is Date and Daily Amount column is each vendor (B, C, D, E, F and ....)

Please help
Below am showing one of the vendors data for reference

ABCDEFG
HelperDateItemQtyRateAmtDaily Amt
01-Apr-2401-Apr-24Chicken2.7210567
01-Apr-24Egg3140420
01-Apr-24L Bonless22805601547
02-Apr-2402-Apr-24Chicken3.1220682
02-Apr-24L Bonless22905801262
03-Apr-2403-Apr-24B Bonless3280840
03-Apr-24Egg31404201260
04-Apr-2404-Apr-24Chicken5.32401272
04-Apr-24Tandoor62001200
04-Apr-24L Bonless22905803052
05-Apr-2405-Apr-24Lollypop2260520520
06-Apr-2406-Apr-24Chicken5.12401224
06-Apr-24Lollypop2250500
06-Apr-24L Bonless2300600
06-Apr-24B Bonless42901160
06-Apr-24Tandoor1021021005584
07-Apr-2407-Apr-24Chicken62501500
07-Apr-24L Bonless23406802180
08-Apr-2408-Apr-24Chicken2.75250687.5
08-Apr-24B Bonless43001200
08-Apr-24L Bonless2.53408502737.5
09-Apr-2409-Apr-24Chicken2.7250675
09-Apr-24Tandoor522011001775
10-Apr-2410-Apr-24L Bonless2.2320704
10-Apr-24Egg31404201124
11-Apr-2411-Apr-24Chicken3.4250850
11-Apr-24Tandoor3220660
11-Apr-24B Bonless13003001810
12-Apr-2412-Apr-24L Bonless43201280
12-Apr-24Chicken52501250
12-Apr-24Tandoor2210420
12-Apr-24B Bonless13103103260
13-Apr-2413-Apr-24
Click to expand...
Am asking very difficult or might not able to explain properly, but guys please help me out
 
Upvote 0
1) What is the naming convention of the sheet, if there's any?
2) Are the columns consistent across all 28 sheets?
3) Are the column headers consistent across all 28 sheets?
4) If I understand correctly, you want all columns A:G to paste into a master sheet. Is this right?
 
Upvote 0
What do you have as source to be clear

1. A workbook with multiple sheets like this
1716388205163.png


2. A folder with Multiple workbooks like this
1716388256701.png


What do you need in the All_in_One sheet, is it like this
BDEFG
DateItemQtyRateAmtDaily Amt
01-Apr-24​
Chicken
2.7​
210​
567​
01-Apr-24​
Egg
3​
140​
420​
01-Apr-24​
L Bonless
2​
280​
560​
1547​
02-Apr-24​
Chicken
3.1​
220​
682​
02-Apr-24​
L Bonless
2​
290​
580​
1262​
 
Upvote 0
Kerryx said:
What do you have as source to be clear

1. A workbook with multiple sheets like this
View attachment 111762

2. A folder with Multiple workbooks like this
View attachment 111763

What do you need in the All_in_One sheet, is it like this
BDEFG
DateItemQtyRateAmtDaily Amt
01-Apr-24​
Chicken
2.7​
210​
567​
01-Apr-24​
Egg
3​
140​
420​
01-Apr-24​
L Bonless
2​
280​
560​
1547​
02-Apr-24​
Chicken
3.1​
220​
682​
02-Apr-24​
L Bonless
2​
290​
580​
1262​
Click to expand...
Thank you sir for response.
Option 1. A workbook with multiple sheets
And in the All_in_One sheet need only Date and Daily Amt below mention
ABCD
DateVendor 1Vendor 2Vendor 3
01-Apr-24
1547​
02-Apr-24
1262​
03-Apr-24
1260​
 
Upvote 0
You could use Power Query
Power Query: 
let
    Source = Excel.CurrentWorkbook(),
    #"Expanded Content" = Table.ExpandTableColumn(Source, "Content", {"Date", "Daily Amt"}, {"Content.Date", "Content.Daily Amt"}),
    #"Filled Down" = Table.FillDown(#"Expanded Content",{"Content.Date"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Content.Daily Amt] <> null)),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Name", "Content.Date", "Content.Daily Amt"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"Content.Date", type date}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Changed Type",{"Content.Date", "Name", "Content.Daily Amt"}),
    #"Pivoted Column" = Table.Pivot(#"Reordered Columns1", List.Distinct(#"Reordered Columns1"[Name]), "Name", "Content.Daily Amt", List.Sum)
in
    #"Pivoted Column"

All_in_One.xlsx
ABCDE
1Content.DateVendor1Vendor2Vendor3
201/04/20241000
302/04/20241100
403/04/20241200
504/04/20241300
605/04/202414002300
706/04/2024150024003500
807/04/2024160025003600
908/04/20244600
1009/04/20242100
1110/04/20242200
1221/04/20243000
1322/04/20243100
1423/04/20243200
1524/04/20243300
1625/04/20243400
All_in_One



Convert each Vendor sheet data to a table and Name each table as Vendor Name you want in the All_in One sheet

HelperDateItemQtyRateAmtDaily Amt
01-Apr-2401-Apr-24Chicken2.7210567
01-Apr-24Egg3140420
01-Apr-24L Bonless22805601000
02-Apr-2402-Apr-24Chicken3.1220682
02-Apr-24L Bonless22905801100
03-Apr-2403-Apr-24B Bonless3280840
03-Apr-24Egg31404201200
04-Apr-2404-Apr-24Chicken5.32401272
04-Apr-24Tandoor62001200
04-Apr-24L Bonless22905801300
05-Apr-2405-Apr-24Lollypop22605201400
06-Apr-2406-Apr-24Chicken5.12401224
06-Apr-24Lollypop2250500
06-Apr-24L Bonless2300600
06-Apr-24B Bonless42901160
06-Apr-24Tandoor1021021001500
07-Apr-2407-Apr-24Chicken62501500
07-Apr-24L Bonless23406801600
 
Upvote 0
Kerryx said:
You could use Power Query
Power Query: 
let
    Source = Excel.CurrentWorkbook(),
    #"Expanded Content" = Table.ExpandTableColumn(Source, "Content", {"Date", "Daily Amt"}, {"Content.Date", "Content.Daily Amt"}),
    #"Filled Down" = Table.FillDown(#"Expanded Content",{"Content.Date"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Content.Daily Amt] <> null)),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Name", "Content.Date", "Content.Daily Amt"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"Content.Date", type date}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Changed Type",{"Content.Date", "Name", "Content.Daily Amt"}),
    #"Pivoted Column" = Table.Pivot(#"Reordered Columns1", List.Distinct(#"Reordered Columns1"[Name]), "Name", "Content.Daily Amt", List.Sum)
in
    #"Pivoted Column"

All_in_One.xlsx
ABCDE
1Content.DateVendor1Vendor2Vendor3
201/04/20241000
302/04/20241100
403/04/20241200
504/04/20241300
605/04/202414002300
706/04/2024150024003500
807/04/2024160025003600
908/04/20244600
1009/04/20242100
1110/04/20242200
1221/04/20243000
1322/04/20243100
1423/04/20243200
1524/04/20243300
1625/04/20243400
All_in_One



Convert each Vendor sheet data to a table and Name each table as Vendor Name you want in the All_in One sheet

HelperDateItemQtyRateAmtDaily Amt
01-Apr-2401-Apr-24Chicken2.7210567
01-Apr-24Egg3140420
01-Apr-24L Bonless22805601000
02-Apr-2402-Apr-24Chicken3.1220682
02-Apr-24L Bonless22905801100
03-Apr-2403-Apr-24B Bonless3280840
03-Apr-24Egg31404201200
04-Apr-2404-Apr-24Chicken5.32401272
04-Apr-24Tandoor62001200
04-Apr-24L Bonless22905801300
05-Apr-2405-Apr-24Lollypop22605201400
06-Apr-2406-Apr-24Chicken5.12401224
06-Apr-24Lollypop2250500
06-Apr-24L Bonless2300600
06-Apr-24B Bonless42901160
06-Apr-24Tandoor1021021001500
07-Apr-2407-Apr-24Chicken62501500
07-Apr-24L Bonless23406801600
Click to expand...
Am not familiar with power query can we use formula please
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
Formula to find location of second row from the bottom containing specific text
Replies
5
Views
76
Peter_SSs
Peter_SSs
F
  • Solved
Formula - Sumif(s)? Sumproduct? etc.
2
Replies
16
Views
278
Fluff
Fluff
N
  • Locked
  • Question Question
Exccel - date formula that drives multiple other formulas
Replies
2
Views
393
Fluff
Fluff
C
  • Solved
Unable to copy formula down sheet if data isn't there.
Replies
3
Views
160
Fluff
Fluff
ExcelNewbie2020
  • Solved
Allocation of Amount, based on the Monthly amount and dates
Replies
4
Views
423
ExcelNewbie2020
ExcelNewbie2020

Forum statistics

Threads
1,223,885
Messages
6,175,183
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top