Forum Discussion
#excel formula - which formula to use to match text that link to the amount and output total in mth
Anyone can help and advice the formula.
I have two spreadsheet need to get the formula to auto total up the amount that matches the item foe the mth.
See sample
How can I get the total amount for April based on Item name in Overall tab from the data in Inform?
Need to match eg Market Talk matching related auto sum all amount for whole month that fall in April from inform tab.
Thanks if anyone can help. Need it urgently.
As the datalist will grow bigger eventually.
Tks
8 Replies
bbsinWhat I understood from your problem.
You want to get the data filled into your first sheet from the second sheet.
The first column of both the information sheets, have some kind of matching texts but not exact.
I think Vlookup should work great in this case . Although Vlookup is good if we have unique values in the column where matching is to be done. If you have office 365 go for Xlookup
And yes we have to use wild character * for the generalization and after the thing is done, I feel that a manual inspection would be needed as these functions fails badly if they capture any comparing value twice.
Again, if sheet was there, it would have been better.
for the reference of examples
vlookup
https://gyankosh.net/msexcel/functions/lookup-and-reference/excel-functions-vlookup/
xlookup
https://gyankosh.net/msexcel/functions/lookup-and-reference/excel-functions-vlookup/
and if the need is to join all the entries, the idea given above is good to create a common code as there are multiple entries with different names.
Nested IF can be applied for the date and that common code and sum can be done.
- Hi gyankosh,
Thanks
I not too sure abt the use of these formula. Will have a look and see if I can get it done. Thanks.
You could pull this off with a pivot table and some creativity. Do you have a sample workbook?
Patrick2788 @mathetes,
Appreciated your help and reply
as I have not control of the description input send over, I not sure if excel is smart enough to pickup up exact text eg in the summary - USS event or UUS that are related to it, calculate the amount relate to it within that month into the spreadsheet.. ha
or I need to clean up the text as per what mathetes has mentioned.
Here's my sample workbook, please feel free to edit and let me know how it can work.
The data tab are not suppose to be sort. 🙂
Regards
You wrote: as I have not control of the description input send over
But I'm hoping you DO have enough control to get whoever it is at the other end of your input data to start being more disciplined about how they enter the data. In the attached, I've added a column that uses data validation to restrict the entries in it to match the acceptable texts for your summary report's budget categories. They can use the adjacent column to freestyle whatever supplemental info they might wish to enter.
It appears you are able to write formulas that will total the amounts per month per category. So if you can get the data entry folks to use a spreadsheet that has data integrity, then you should be home.
You might also find @Patrick2788's suggestion of the Pivot Table to work even more easily than writing all those formulas to summarize these categories. I have my own personal budget tracking spreadsheet set up to take thousands (over the course of a year) of entries, from multiple credit card and bank accounts, and summarize the data--after I standardize each expense or income into a set of consistent budget categories--via a Pivot Table. No formulas are required; it updates easily.... You just have to have the raw data created following some sort of disciplined, consistent way. And data validation, using a table taken from the acceptable categories, will give you that.
A postscript to what I wrote before, regarding cleaning up the data to make matches so much easier.
Let's take your "Market talk" budget category as an example. In the "Inform" sheet, whoever is filling that in has been adding "event 1" or "event 2" and so on, for tracking purposes or whatever. THAT kind of information, if needed, should be made as an entry in a different column. The primary column could be headed "Budget Category" with a supplemental column (or two or three) that could be used to store such things as "event at Town Hall" or "speaker: Jane Doe" --things that are useful to know, but NOT part of the accounting procedure per se.
And then, to help with data integrity (i.e., such things as spelling consistency), there's a feature of Excel called "Data Validation" --you can see it here on the far right of this image.
Use data validation to make sure that entries under the Budget Category column are ONLY selected from an approved list. Then they'll always match.
To what extent are you able to make fundamental changes to how these data elements are collected in the first place?
The basic formulas you are looking for are fairly simple, but your data are (sorry) something of a mess, and that will make it tricky unless it's all cleaned up.
What do I mean by "mess"? A few examples
- in your budget sheet, you list "Market Talk" as the first item, but on the Inform sheet, it's "Market Talk - Event 1" ---> so although the first two words match, the cells themselves don't. It could be accommodated, but it'd be better to not have to complicate things like that.
- in your budget sheet, you list" HR - Claim" but in Inform it's "HR claims" You and I as human beings will see that as the same; a computer sees it as quite different
- and so on
If you upload an actual spreadsheet instead of an image, it would be easier to give you an example of a working formula, but you really truly need to look into cleaning up your data so matches can be made.
