Excel data on multiple lines

Question

I have a very large spreadsheet (9k lines) from a bank.

Some (online payment, deposit) lines are 1 line: Date, Merchant, Amount - those are easy

However, some (when using card to purchase) have 2 or 3 lines with the first line being date , some junk (address, trans# etc) with the merchant name being on line 2

Sample data:

30-Nov	Preauthorized Credit xxx 181130		$500
3-Dec	Deposit		$100
3-Dec	POS Purchase POS Purchase TERMINAL 58378001		$15.50
	DOLLAR TR 126 MAIN ST   location  XXXXXXXXXXXX9181
	SEQ # 120109202208

In the above data, the top 2 lines work perfectly as it's only 1 line. However, the 3rd has the date and amount one the first line but the merchant on the second

Toying around with this, I concatenated B3 + B4 putting that in column C3 which looks like

a b c d

30-Nov	Preauthorized Credit xxx 181130		$500
3-Dec	Deposit		$100
3-Dec	POS Purchase POS Purchase TERMINAL 58378001	POS Purchase POS Purchase TERMINAL 58378001 DOLLAR TR 126 MAIN ST   location  XXXXXXXXXXXX91	$15.50
	DOLLAR TR 126 MAIN ST   location  XXXXXXXXXXXX9181	DOLLAR TR 126 MAIN ST   location  XXXXXXXXXXXX9181 SEQ # 120109202208
	SEQ # 120109202208

While this does help, I still have to do a manual inspection of all lines to make sure none slip through the cracks.

If there is any way to do this better or automate, I would be very appreciative.

Answer 1

Hi,

Just share 2 tales - input and expected result. Also, how does one know what the merchant name is, in a string. Please clarify.

Answer 2

So when I created this thread, the response snow gave was PERFECT

I'm doing more work but with different Cell #'s. I thought I compensated for the new cell#'s in the formula

However, the result is a blank line (which If I'm correct means none of the "if's passed

Cell D2 is date Cell E2 is merchant name (which may or may not span few lines)

Date	Merchant
12/18/2017	POS Purchase POS Purchase TERMINAL 06478785 TIRE SHOP
	xx XXXXXXXXXXXX2661 SEQ # 121611005414
12/18/2017	Electronic Payment VZ WIRELESS VE VZW WEBPAY 171218
12/19/2017	POS Purchase POS Purchase TERMINAL 24721001
	TJMAXX #0 10 WASHI NGTO xx
	XXXXXXXXXXXX2661 SEQ # 121918410254

The formula I used:

=IF(D2="","",IF(AND(D3="",D4=""),E2&CHAR(10)&E3&CHAR(10)&E4,IF(D3="",E2&CHAR(10)&E3,E2)))

I'm sure I'm missing something basic. Any help appreciated

Answer 3

Formula in C2:

=IF(A2="","",IF(AND(A3="",A4=""),B2&CHAR(10)&B3&CHAR(10)&B4,IF(A3="",B2&CHAR(10)&B3,B2)))

Best Regards,

Snow Lu