• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

List consolidation with VBA

B

bluetaurean

Member
Hello,

Can anyone help with VBA script to conlidate ranges as below

First Range

Day|Product|Qty

1|ABC|99

1|PRQ|88

2|DEF|55


Second Range

Day|Product|Qty

1|EFG|33

2|HIJ|44

2|klm|22


Output Reqd

Day|Product|Qty|Day|Product|Qty

1|ABC|99|1|EFG|33

1|PRQ|88|""|""|""

2|DEF|55|2|HIJ|44

""|""|""|2|klm|22


In reality I have about 15 different lists to be consolidated.

regards

oscar
 
Hi Oscar ,


Can you clarify the following :


1. Number of ranges to be consolidated i.e. is it only 2 ranges at a time , or will you need to consolidate three or more ranges ?


2. Are the input ranges on the same sheet , and is the output required on the same sheet ?


Narayan
 
Hi Oscar,


As per my understanding of your query, I have written below code for you:


Sub Consolidate_Range()

ThisWorkbook.Activate

Dim sht As Worksheet

i = 0

For Each sht In Worksheets

If sht.Name <> "consolidate" Then

sht.Activate

lc = Cells(1, 1).End(xlToRight).Column

lr = ActiveSheet.UsedRange.Rows.Count

Cells(1, 1).Resize(lr, lc).Copy

Worksheets("consolidate").Select

lcc = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column

Cells(1, lcc + i).PasteSpecial

i = 1

End If

Next

End Sub

Copy the above code and paste in Module and rename the sheet to "consolidate" where all the collated lists will be pasted. This will work when you collate list within the workbook.


Let me know if you required something else.


Thanks & Regards,

Anupam Tiwari
 
@ Narayan,

there are 12 ranges which need to be consolidated. all of them are on a single sheet. The length of each range varies, breadth is always 3


Output can be on the same or different sheet.

thanks


@Anupam I will try your code. Please note all input ranges are in a single sheet. Also the output needed is such that the days numbers must align. i.e one day may have 2 entries in one range, 3 entries in another, and nil in another. the corresponding cells must appear blank in the consolidated range.


Thanks guys.
 
Hi Oscar,


Unable to clearly understand your query.


Could you please share your data sheet so accordingly I can try to write code for you or other Excel experts can help you out.


Regards,

Anupam Tiwari
 
Hi Anupam,


Please find attached a link to the file.


http://dl.dropbox.com/u/29802745/samplefiles.xlsx


Appreciate your help.


regards
 
Hi, bluetaurean!


Regarding your desired output:

Output Reqd

Day|Product|Qty|Day|Product|Qty

1|ABC|99|1|EFG|33

1|PRQ|88|""|""|""

2|DEF|55|2|HIJ|44

""|""|""|2|klm|22


I have these still not understood points:

a) the consolidation you're talking about is of tow products per row only?

b) are you losing the Line(n) reference in your uploaded sample file?

c) why did you used a two column example in your first post and a three column example in your file? does it depends on the no. of lists?

d) wouldn't these be more desirable outputs?

1)

-----

Day|Product|Qty|Day|Product|Qty|Product|Qty

1|ABC|99|1|EFG|33|PRQ|88

2|DEF|55|2|HIJ|44|klm|22

-----

[pre]
Code: 
Date	Product	Qty	Product	Qty	Product	Qty	Product	Qty	Product	Qty	Product	Qty	Product	Qty	Product	Qty
01/10/2012	Apple	 10 	 Kiwi 	 20 	 Melon 	 7 	Mango	 15 	 Pomegranate 	 30 	 Pear 	 10 	Orange	 20 	 Peach 	 40
02/10/2012	Apple	 30 	 Kiwi 	 60 	 Melon 	 20 	Mango	 10 	 Pomegranate 	 20 	 Pear 	 7 	Orange	 20 	 Peach 	 40
03/10/2012	Apple	 18 	 Kiwi 	 36 	 Melon 	 12 	Mango	 16 	 Pomegranate 	 32 	 Pear 	 11 	Orange	 45 	 Peach 	 90
[/pre]
-----


Regards!
 
Hi SirJB7,


Ok i was not very clear. I have uploaded another file which will perhaps clarify


http://dl.dropbox.com/u/29802745/List.xlsx


a. there is a data sheet which has Day;Product;ProductionLine;Qty

b. Produciton line indicate the machine on which the product is produced.

c. a production plan is to be issued. first option is a pivot table of the list, but this output is very elaborated(too long). In reality there are 12 productin lines all producing differnt products.

d. the desired output is shown in another sheet

e. date or day can be a day number or acutal date whichever is simpler to implement.


Regards
 
Hi Oscar ,


Can you check out this file ?


http://speedy.sh/tsabv/List-1.xlsx


I have filled in formulae in columns A , B and C on the RequiredOutput tab.


The formulae depend on the following :


1. Data is sorted on the following columns : ProductionLine , Day , Product.


2. There are no duplicate records i.e. there should not be duplicate entries where the ProductionLine , Day and Record ( all three ) are the same.


Please not that in case the number of records is very high , the formulae can affect the calculation time.


Narayan
 
You must log in or register to reply here.
Back
Top