I have a large dataset of transactions that I want to organize by type of transactions. The only way to know what type of transactions they are is to keyword filter them in a column of transaction descriptions. The descriptions are not uniformly coded and oftentimes, you will not find the keyword you are looking for in the description column although that transaction matches what is being searched.
For example, if I want to label all transactions that have to do with "uniforms," I will keyword filter the description column by "uniform." However, there will be transactions that are described as "shirts" or "workwear."
I have been doing a lot of manual scrubbing to group these transactions. However, I wanted to know if there was a more automated way of doing this.
To be specific, this is the procedure I currently follow:
1) Pivot the data
- Columns: quarter
- Rows: transaction description text
- Values: invoiced spend
2) Alphabetize data
3) Group together rows that belong together
- This creates a "transaction description text 2" field that precedes "transaction description text" under 'Rows'
4) Keyword filter the "transaction description text" field
- Continue grouping
- There are many rows (transactions) that are left ungrouped
5) Highlight one grouping and scroll down the pivot table highlighting relevant rows and Shift + F10 + Group
- I do this for each grouping until there are no ungrouped transactions left
Open to any suggestions! Thanks!
For example, if I want to label all transactions that have to do with "uniforms," I will keyword filter the description column by "uniform." However, there will be transactions that are described as "shirts" or "workwear."
I have been doing a lot of manual scrubbing to group these transactions. However, I wanted to know if there was a more automated way of doing this.
To be specific, this is the procedure I currently follow:
1) Pivot the data
- Columns: quarter
- Rows: transaction description text
- Values: invoiced spend
2) Alphabetize data
3) Group together rows that belong together
- This creates a "transaction description text 2" field that precedes "transaction description text" under 'Rows'
4) Keyword filter the "transaction description text" field
- Continue grouping
- There are many rows (transactions) that are left ungrouped
5) Highlight one grouping and scroll down the pivot table highlighting relevant rows and Shift + F10 + Group
- I do this for each grouping until there are no ungrouped transactions left
Open to any suggestions! Thanks!
