Macros to lookup column and row headings and extract data

Hello,

In the attached spreadsheet I have a list of customers along with the assoiated legal firms. Each tab represent a different month. The customers are listed in rows and the legal firms are in columns across the sheet. However, the customer and legal firm list is different for every month. I want to create a summary sheet which will keep a running total for each customer and legal firm. Is there a macro that can scan through all the sheets in the workbook and extract all the customer names as well as all the legal firms and drop them in the summary tab. I dont want to have duplicates as some names will appear in mulitple tabs. Then I think I will need a vlookup that will sum up the amounts for each customer and legal firm. If there's a macro that can sum up the amounts for those names, that would even be better. Any help anyone can provide me would be greatly appreciated!

Thanks!

I have created a master sheet which consolidates all sheets in to it.
Please note the following:
The code works by matching the headings in the master to the headings in the individual sheets. I have created the legal firm names on row 1 of the master sheet. I have also deleted row 1 in some sheets as it is blank. For the code to work all headings must be on the same row, but not necessarily on the same column.
You can amend the headings in the master sheet as long as these heading match the headings in the individual sheets.
You have legal names with figure. You have also names with no figures. I put all the headings with no figures at the back of the columns so that their figures will not be added.

You have left lots of issues unknown so my assumption may not work, but try the attached.

Hi AB33,

First off, thank you so much for doing this! This is very helpful to me. The first customer listed on the master tab is Mary Gordon / Urban Affordable. There was two invoice (2702 & 2787) for that customer in the February 2013 tab but the code only picked up invoice (#2702). Same situation with customer Urban Affordable / Mary Gordon in the August 2013 tab, only picked up the first invoice but not the second. Can you help me out? I'd like the code to pick up both invoices.

You mentioned that you listed all the legal firms on the first row of the master tab. Now if there's a new firm, do I need to add the new firm to the master sheet or will the code be able to automatically pick it up? You also mentioned that I've left lots of issues unknown, do you mind sharing w/ me some of the issues I need to consider?

Again, I really appreciate your help on this matter!

Sorry!
I have got my columns count wrong. It should be 17 columns.

Under normal circumstance, you should have all your headings ready in the sheet you want to consolidate, but there is none in this case, so I had to make a guess.
Part of the reason why I have got my columns count wrong is I kept adding new names, but the other main issue is:
Not all columns (Firms) you want to add. For e.g. if the column has a text. So, I had to separate the columns with text and numbers. Columns A-C are text, so the rows are not added.
Ideally, you should have all the firm names on side of the sheet, let's say on the right and firms on the left. The reason be the code easily add the figures of the firm, but only picks up one unique customer per row.
You can add as many new names as you wish, but the new headings should have corresponding match in individual sheets.
Right now to speed the code, My code columns in master stretches to column Z, but can easily increase to what ever column you want.

Try it and will update it.

Hey,

You seem to be an expert at this. I'll have to look at the revised file above later, right now I am working on another spreadsheet. I think it'll be quick for you. Below is the macro that I have in the spreadsheet right now. Essentially there's a dropdown on column 26 and if a user selects one then that row will be copied to another sheet. However, I am trying to add another condition. Let's say column 4 is either "yes" or "no." Then based on the selection in column 26 & column 4 the row will be copied to the appopriate tab. How can I add another condition to the if statement?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 26 Then
If Target.Value = "AWC" Then
Target.EntireRow.Copy Destination:=Sheets("Approved & Waiting to Close"). _
Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
End If
End Sub

Please use code tags with your code as per forum's rule.
This is not related to the original thread and strictly speaking, it should require starting a new thread.

I do not know what is the relevant of this line

If Target = "AWC" Then
Please delete it if it does not require.

Please Login or Register  to view this content.

Hey AB33 - sorry this is my first time posting to the forum, I didn't know about the code tags. I know I should've prob posted a new thread but you've been so helpful and prompt on your replies I figured you could help me out. That's exactly what I am looking for. Thank you so much!!!

Dan

Dan,
Do not worry!
I have done the same when I joined the forum.

I am sorry for my lack of knowledge on this matter but I am having trouble adding onto the code you provided me. I'd like to add addtional conditions, for example if column 26 is "DWC" and column D is "Yes" then copy the row to the "Denied With Counter" tab. I tried using ElseIf but it doesn't work. Please help me...

Hi AB33,

Finally getting around to the updated spread sheet that you sent over. So looks like if there a new legal firm, I have to add it to the master sheet in order for the code to pick it up. In the attached I added september 2013 tab for testing. I put in two legal firms and two customer. For some reason, the macro picked up the first customer but not the second. Can you take a look and tell me why that is?

Thanks,
Dan

Dan,
There is no consistent with the layout of the sheets.
All sheets have "Total" at the bottom of each sheet, but not with the latest(Sep) you have just added.
If the code adds the total for each customer, I assumed we need to remove this total copied in to the master sheet. You may need a single "Total" line in the master sheet. So, I have excluded the last row with the data in each sheet so as to exclude the "Total" row. Now the Sep sheet does not have "Total" row, so the code assumed the last row with the data is row 2,hence the exclusion of the row 2 (like the Total for each sheet).
I do not know if that what you were intended to, but if you want to add the Total for each tab in to the master sheet, I can change the code, so all rows(Including the Total row) will be copied in to master sheet.

Hi AB33,

I am finally getting around to this, I've been pulled away for other projects. Thank you for responding! I was testing out the code by adding new legal firms/tab and see if it would pull it into the master tab. I know what you mean now...I added a total line on row 36 of the Sept 2013 tab and now it pulls in the second customer. The Feb 2013, March 2013, and April 2013 tab has the total line on different rows. I thought the total line had to be on row 36 for each tab?

What I am seeing are some customer appearing more than once in these tabs so I was wondering if you could modify the code to look up the invoice # instead of the customer name and then pull in all the information that is tied to that invoice #? I think this way I'll be able to capture all the legal fees tied to that customer.

Again, I really appreciate your help in this matter.

Thanks,
Dan

I do not know what the final result looks on your actual data, but I have changed the master data to be merged based on column B (invoice No). Test it and see what it looks like.

Hi AB33,

Thank you for making the change! I didn't realize this before but I am seeing that there's one invoice with two customers on it or there are customers with no invoice # assoiciated with it. Sorry for my lack of knowledge in coding here but is there a way for the code to pull all rows except the "totals" row from each sheet and drop em in the master sheet. The data from the rows would be matched up against the heading on row 1 of the master tab. Any thoughts?

Dan

I have removed the second part of the code which deals with merging.