Summarize/group large amounts of data

[dmfweb]

I am trying to work on budgeting, so I downloaded my bank statements for the year into a CSV. Is there any way to create a column that I can make a pivot table from? Most entries are a little bit different “Target” and “target stores” for example.

How can I get those both to group as target?
Even if I had to create some sort of matrix for the bigger chunk of different places I would be OK with that but I don’t know if you can use “contains“ with multiple values etc.… I’m not 100% sure this makes sense. Lol
There are thousands of lines of data so thinking about doing this manually makes me want to cry as well as looking at the thousands of lines of data of where the money goes does also. Lol

 

[jaeiow]

Classic. This is one of the great contradictions of excel. And doing some research, there is a whole industry around it. Look up Tiller, although I am not endorsing another subscription. I wonder how powerful the Tiller autocategorization actually is. I would recommend you update your settings because there are different functions available for these purposes. SEARCH works on all versions, though and it would get you what you want.

 

[Joe4]

Classic. This is one of the great contradictions of excel.

I am not sure what you mean by that.
It seems to be a data discrepancy issue, not an Excel issue.
Inconsistent/bad data is an issue for any system.

I am trying to work on budgeting, so I downloaded my bank statements for the year into a CSV. Is there any way to create a column that I can make a pivot table from? Most entries are a little bit different “Target” and “target stores” for example.

How can I get those both to group as target?

If it is just a few defined options, then you could probably simply use a Find/Replace to standardize all your data.
If there are a bunch (i.e. not just "Target" entries, but other stores that need combining as well), it gets a bit trickier, especially if you do not have some master list of what you want it all boiled down to.
If you do, then you may be able to use some "Fuzzy Matching" logic, like found here: Alan's UDFs for the Fuzzy Match problem

 

[dmfweb]

Classic. This is one of the great contradictions of excel. And doing some research, there is a whole industry around it. Look up Tiller, although I am not endorsing another subscription. I wonder how powerful the Tiller autocategorization actually is. I would recommend you update your settings because there are different functions available for these purposes. SEARCH works on all versions, though and it would get you what you want.

Thank you for the reply. I can't seem to find a way to use search with multiple values (Walmart, Target, Macys) and have it return a value to group by. Am I missing something?

 

[jaeiow]

Well the amount of time it takes to categorize data in Excel cells is not worth the effort.

Thank you for the reply. I can't seem to find a way to use search with multiple values (Walmart, Target, Macys) and have it return a value to group by. Am I missing something?

This is better done in power query conditional columns.