I downloaded a credit-card summary statement. Each row represents a purchase (including date, store name and amount). A given row of data might look like this:
<tbody>
</tbody>
I want to add a fourth column that automatically qualifies a customized "store-type" (e.g., grocery, hardware, clothes, art-supplies, etc.) based on the name of the store listed in column 2.
Nested IF statements might work, but formulas become cumbersome when there are many stores. Moreover, data in the 'store' field often includes non-essential information, so wildcards will be necessary.
I'm imagining that I will have to use an IF function in an array format which references a separate dynamic list that includes store name (col 1) and store type (col 2) to return a value.
I imagine it working this way: The formula in column 4 looks for a text fragment in column 2 (e.g., "Publix*") and searches a separate dynamic list by store name and returns the associated store-type to column 4.
<tbody>
</tbody>
An array formula is the only thing I can think of that is powerful enough to manage such complex data. Suggestions?
1/1/12 | Publix #246 SR98286659 | $128 |
<tbody>
</tbody>
I want to add a fourth column that automatically qualifies a customized "store-type" (e.g., grocery, hardware, clothes, art-supplies, etc.) based on the name of the store listed in column 2.
Nested IF statements might work, but formulas become cumbersome when there are many stores. Moreover, data in the 'store' field often includes non-essential information, so wildcards will be necessary.
I'm imagining that I will have to use an IF function in an array format which references a separate dynamic list that includes store name (col 1) and store type (col 2) to return a value.
I imagine it working this way: The formula in column 4 looks for a text fragment in column 2 (e.g., "Publix*") and searches a separate dynamic list by store name and returns the associated store-type to column 4.
1/2/12 | Publix #246 SR98286659 | $130 | Grocery |
1/3/12 | Home Depot #5 208909 dlsf | $100 | Hardware |
1/4/12 | Publix #16 LKO kjnlksld 22335 | $80 | Grocery |
<tbody>
</tbody>
An array formula is the only thing I can think of that is powerful enough to manage such complex data. Suggestions?