If a match, then enter corresponding value

[KristenG]

I work for a nonprofit and we have to complete lengthy excel workbooks, much of which i have begun to automatize to reduce duplicate data entry. But I am stuck on this one and appreciate your help. I'm using generic cell titles to keep things simple and relatable. Here is a google version since the forum isn't letting me upload the excel file

(it's the drive.google.com/file address followed with: /d/1jMCWJt7wimRQQ84F2eTThy3jzm2qKEQM/view?usp=sharing )

Imagine 2 worksheets from a bigger workbook. First worksheet "Primary Chart" has customer IDs auto inputted from data entered earlier in the workbook. There is the option to add Total Purchases next to each customer ID.

Said Purchase details are on the 2nd worksheet, again with Customer IDs listed (aka Data Source). Not all customers listed in the primary chart will have purchased something.

Goal is to have any purchase totals from 2nd worksheet Data Source over to the Primary Chart, entered next to the corresponding customer ID.


I tried if/then formulas but was having to account for every possible customer ID so i know that's not the solution. Any help would be greatly appreciated!

Thank you,

[etaf]

try a SUMIF()

=SUMIF( Range with customer ID in 2nd worksheet, customer ID in first work sheet, Range to sum on 2nd worksheet)

can not open the example spreadsheet
See the yellow banner above to attach a sample, try creating a small sample sheet with NO REAL info

Heres a sample of above

[mehmetcik]

If you have the latest version of Excel then.

=SORT(UNIQUE(Customers!A:A))

Would return a sorted list of your customers from Column A of the Customers List.
One issue that the list ends with a 0 which refers to the empty cell in Column A.

You can either ignore that or use a method to find the last used cell in column A
=SORT(UNIQUE(INDIRECT("Customers!A2:A"&MATCH("zzzzzzz",Customers!A:A))))

You can then use the SumIf function to return the sales by customer
=SUMIF(INDIRECT("Customers!A2:A"&MATCH("zzzzzzz",Customers!A:A)),A2,INDIRECT("Customers!C2:C"&MATCH("zzzzzzz",Customers!A:A)))



Ok the next problem is that if you add customers on the Customer List, The sorted unique list is updated automatically.

However you have to manually extend the Sumif Formula.

There is normally an easy solution to that. Use a table. The table will extend your formulas automatically....... Nope Not if it uses the Spill feature.

So Modify your formulas.

A2 =INDEX(SORT(UNIQUE(INDIRECT("Customers!A2:A"&MATCH("zzzzzzz",Customers!A:A)))),ROW()-1)
B2 =IF(A2="","",SUMIF(INDIRECT("Customers!A2:A"&MATCH("zzzzzzz",Customers!A:A)),A2,INDIRECT("Customers!C2:C"&MATCH("zzzzzzz",Customers!A:A))))

Now you can use a table.

Just Checked the table and the formulas are not extended automatically. hmmm.
I will leave the table there while I play around.

Are you allowed macros? I could easily use a macro to extend the table.


This Macro will expand your table automatically.

Please Login or Register  to view this content.

This Macro will run the ExpandT Macro automatically it is sheet specific.
Right Click On Your Sheet Name "Customers" At the Bottom Of Excel and Select View Code to see it.

Please Login or Register  to view this content.

[KristenG]

OH MY GOD! This works perfectly. I have just been staring at it for the last thirty minutes, marveling, trying to figure out how it works its magic. Thank you. I've updated the workbook accordingly. You have indirectly helped the homeless by helping me get this workbook in better working order.

[KristenG]

Thank you very much for taking the time to look through this. I'm actually going to borrow the tricks you did for a different task at hand. Much appreciated. You have indirectly helped the homeless today for my nonprofit employer=) Thank you!