Aligning cells into the same column based on similar values

I recently pulled some data into excel and some of the cells ended up like : {paypal:34, giftcard: 34, authorizenet: 44} (for payment methods)

I managed to divide them up into separate columns as such:

paypal: 34 | giftcard: 34 | authorizenet: 44 |

but not all lines have the same categories. Some have less payment methods while others have more. So I basically have a large table of

Is there a way to align the cells in each row where if they contain "paypay", they align into a single column and so on? I was thinking about sorting but they won't exactly line up.

Thanks!

Solution

You should start by using the 'Text to Columns' functions in excel.

First, select all your data then go to: Date > Data Tools > Text to Columns.

Next, select 'Delimited' > Next >

Next, check only 'Comma' > Next >

Finally, Click 'Finish'

Now you have all of the different payment types in separate columns/ cells and need to 'align' all your 'Paypal' in the same column and 'Authorize.Net' in the same column and so on. Use the following formula to get this 'alignment' part done:

=IF(LEFT(C7,6)="Paypal",C7,IF(LEFT(D7,6)="Paypal",D7,IF(LEFT(E7,6)="Paypal",E7,"")))
=IF(LEFT(C7,6)="Giftca",C7,IF(LEFT(D7,6)="Giftca",D7,IF(LEFT(E7,6)="Giftca",E7,"")))
=IF(LEFT(C7,6)="Author",C7,IF(LEFT(D7,6)="Author",D7,IF(LEFT(E7,6)="Author",E7,"")))

You will need to edit the directly reference cells that I show above to fit your own worksheet needs. My parsed (after splitting my data from delimiters) data was in columns C, D, and E. Therefore, I used C7, D7, and E7 to use the 'left' formula on. These formulas on my worksheet were in cells G7, H7, and I7. Give is a try and let me know.

Tip: on the 'Left' formula, make sure the text string is exact (capitals, spaces, etc).

Good luck!