VBA Code to find value in one column then go to other column

[bmkelly]

Hello,

I have a Workbook with multiple Sheets and I was wondering if there is a code that could help me find and insert data I need instead of filtering/copy and paste that we have been doing for this project.

This is just one of many Account Numbers that I grabbed to share but I would like the data to basically vlookup the Account Number from our Master Sheet to this Raw Data Sheet in this example below and then Sum the Customer Invoice Amount by Modality Group. Bare in mind this just one example of a matching Account Number from the Master to the Raw Sheet as there are numerous different Account Numbers.

Number	Modality	Number	Expense Line Type	Start Time	End Time	Labor Hours	Quantity Sold	Unit Price	Customer Invoice Amount
ACCT0101010	RADIOLOGY	EXP12345678	Labor	2021-11-22 15:00:00​	2021-11-22 18:30:00​	3.50​	3.50​	$ 295.00	$ 1,032.50

ACCT0101010

RADIOLOGY

EXP11223344

Labor

2021-12-14 14:00:00​

2021-12-14 16:45:00​

2.75​

2.75​

$ 230.00

$ 632.50

ACCT0101010	BIOMED	EXP2345679	Labor	2021-11-18 08:30:00​	2021-11-18 09:00:00​	0.50​	0.50​	$ 105.00	$ 52.50
ACCT0101010	LABORATORY	EXP34567890	Labor	2021-11-19 13:30:00​	2021-11-19 14:00:00​	0.50​	0.50​	$ 105.00	$ 52.50

ACCT0101010

BIOMED

EXP22334455

Labor

2021-11-22 09:00:00​

2021-11-22 10:30:00​

1.50​

1.50​

$ 105.00

$ 157.50

ACCT0101010

LABORATORY

EXP33445566

Labor

2021-11-23 14:30:00​

2021-11-23 15:00:00​

0.50​

0.50​

$ 105.00

$ 52.50

This is one row of example data of our Master Sheet that we will want the sum data to be pasted into. BioMed Modality Sum in the Biomed Invoiced Column, IT Modality Sum in the IT Invoiced Column Etc.

Account	Number	Parent	Parent's Parent	Total Invoiced in last 12 Months	Biomed Invoiced in last 12 Months	IT Invoiced in last 12 Months	Lab Invoiced in last 12 Months	Rad Invoiced in last 12 months
DOCTORS	ACCT0101010			$ 8,750.00

 

[Deek6t8]

If your data was in a table called Table2 the formula would be =SUMIFS(Table2[Customer Invoice Amount],Table2[Number],M3,Table2[Modality],"BIOMED") M3 would be the account number

 

[Deek6t8]

You could add additional parameters to the sumifs formula for date before and after certain dates too

 

[bmkelly]

If your data was in a table called Table2 the formula would be =SUMIFS(Table2[Customer Invoice Amount],Table2[Number],M3,Table2[Modality],"BIOMED") M3 would be the account number

View attachment 78466

Thanks for the response! I see what you are onto but I run into the issue that the Account Numbers not lining up between sheets. If we have the Account Number set to giving cell (M3, M4, M5 etc.) it will only look at that specific cell. When I go to my Raw Data Sheet my Account Numbers are in Column A (your M), right now I manually copy the Account Number from the Master Sheet and Paste into the Account Number Filter, I then will filter each Modality and then sum the Customer Invoice Amount by Modality and type/paste that into the appropriate Cell on the Master Sheet (Columns I-L). I am basically wanting those cells in Columns I-L to look at the Account Number from the Master Sheet (Column D) and find that in the Raw Data Sheet (Column A), I then want it to group the Modalities together (BIOMED, IT, LABORATORY and RADIOLOGY) and Sum the Customer Invoice Amount from those Modalities and insert that number into the Master Sheet (Columns I-L Biomed, IT, Lab and Rad) in the appropriate cell for that Account Number.
Hopefully that may clear some confusion if there is any!