Extracting / Calculating data from multiple worksheets into one Master Worksheet

[srini82]

Hi

I'm new to using Macros. So need your help.

I have 2 huge data files from which I need to consolidate into one.

I think its better to use macros to do this for me as writing a formula and running them each time is time consuming taking into account the size of the data.


Sample data as follows:

Sheet 1.

Id Name No

1 A 10

2 B 20

3 A 11

4 B 21

5 C 15


Sheet 2.

Id Description Amount

1 Revenue 1000

1 Expenses 600

1 Gross profit 400

1 Tax 20

1 Net Profit 380

2 Revenue 900

2 Expenses 500

2 Gross profit 400

2 Tax 20

2 Net Profit 380

3 Revenue 950

3 Expenses 550

3 Gross profit 400

3 Tax 20

3 Net Profit 380

4 Revenue 950

4 Expenses 550

4 Gross profit 400

4 Tax 20

4 Net Profit 380

5 Revenue 800

5 Expenses 400

5 Gross profit 400

5 Tax 20

5 Net Profit 380


Sheet3. Master Worksheet (Need macros to produce below result)


Id Name No Revenue Expenses Gross profit Tax Net Profit

1 A 10 1000 600 400 20 380

2 B 20 900 500 400 20 380

3 A 11 950 550 400 20 380

4 B 21 950 550 400 20 380

5 C 15 800 400 400 20 380


Criterias:

Id - Should be from Sheet 1

Name - Should be from Sheet 1

No - Should be from Sheet 1

Revenue / Expenses/Gross Profit / Tax / Net Profit should be from Sheet 2 if it matches the 'ID' as in Sheet 1.

 

[Hui]

Srini82


In Sheet3!A2 put:

=Sheet1!A2

Copy across to Column C

Then Copy down


In Sheet3!D2 put: =SUMPRODUCT((Sheet2!$A$2:$A$1000=$A2)*(Sheet2!$B$2:$B$1000=Sheet3!D$1)*(Sheet2!$C$2:$C$1000))

Copy across to Column H

Then Copy down


Adjust the Row 1000 to suit your data

Make sure that the values in Sheet2 Column B match the values in Sheet3 Row 1

 

[sdsurzh]

Hi,


Can you please upload the same file.


Thanks,

Suresh Kumar S

 

[srini82]

Hi Hui


Thanks for your solution.

But I have 100,000 rows in each Sheet (Sheet 1 & Sheet2) with an ODBC link and I don't want to change anything in these sheets. Tried your formula myself but it is taking a long time.


Instead if we have macros which might help to consolidate such data faster.


Suresh - I'm not sure how to upload a file here.. Sorry I'm quite new here..


Regards

Srini

 

[SirJB7]

Hi, srini82!


About your question...


SUMPRODUCT function is a volatile function, that's to say it should be calculated any time any cell is changed in the worksheet, not just cells of the dependency list chain. So with 100K rows in 2 sheets, it'd be expected and reasonable that it takes a long time to calculate. And you're right, maybe macros do the job faster.


About uploading a file...


Perhaps you'd want to read the three (or four) green sticky posts at this forums main page so as to know the guidelines that will lead to know how this community operates (introducing yourself, posting files, netiquette rules, and so on).

Consider uploading a sample file (including manual examples of desired output), it'd be very useful for those who read this and might be able to help you. Thank you.

Give a look at the second (or third, Chandoo is always changing its numbers so I get confused) green sticky post at this forums main page for uploading guidelines.


Regards!

 

[sdsurzh]

Hi Srini,


You can upload the same workbooks from the below links.


http://www.speedyshare.com/

http://www.2shared.com/


Thanks,

Suresh Kumar S

 

[srini82]

Hi SirJB7 & Suresh


Thanks for the suggestion.


http://speedy.sh/eAUJT/Macro-Issue.xlsx


Hope it works


Regards

Srini

 

[Krishnakumar]

Hi


Try this. Adjust the sheet names

[pre]

Sub kTest()

Dim ka, i As Long, n  As Long, dic As Object

'//IDs
ka = Worksheets("Sheet1").Range("a1").CurrentRegion.Resize(, 3).Value2  '<<== adjust the sheet name

Set dic = CreateObject("scripting.dictionary")
dic.comparemode = 1

For i = 2 To UBound(ka, 1)
If Len(ka(i, 1)) Then
dic.Item(ka(i, 1)) = ka(i, 1) & "|" & ka(i, 2) & "|" & ka(i, 3)
End If
Next

Erase ka

ka = Worksheets("Sheet2").Range("a1").CurrentRegion.Resize(, 3).Value2  '<<== adjust the sheet name

For i = 2 To UBound(ka, 1) Step 5
For n = i To i + 4
If dic.exists(ka(n, 1)) Then
dic.Item(ka(n, 1)) = dic.Item(ka(n, 1)) & "|" & ka(n, 3)
End If
Next
Next

With Worksheets("Sheet3") '<<== adjust the sheet name
.Range("a1").CurrentRegion.Resize(, 8).ClearContents
.Range("a1").Resize(, 8) = [{"Id","Name","No","Revenue","Expenses","Gross Profit","Tax","Net Profit"}]
.Range("a2").Resize(dic.Count) = Application.Transpose(dic.items)
With .Range("a2").Resize(dic.Count)
.TextToColumns .Cells(1), xlDelimited, , , , , , , 1, "|"
End With
End With

End Sub

[/pre]

Hit Alt+F11 > Go to insert > Module and paste the code.


Hit Alt+Q to close the VBE window. Hit Alt+F8 and Run 'kTest'


Kris