Excel: Combining several columns of Dates & Currencies

  • Thread starter Thread starter Healingbear
  • Start date Start date
H

Healingbear

Hello all and thanks for any assistance...

I have a spreadsheet containing 6 columns:
AB, CD, and EF where A, C, and E are reciept dates and B, D, and F ar
the corresponding receipt totals.
I'm trying to create two more columns (say GH) that list all th
non-zero contents of the preceding 6 columns, G for all the dates and
for all the corresponding totals.

I've exhausted my brain at a solution (excessive IF statements appeare
to be my only hope).

Any assistance would be appreciated.
I'd take the final columns in any order, but can they even be sorted b
total value (largest to smallest)?

Thanks again,
A grateful and appreciative newbie,

Healingbea
 
Hi Healingbear!

Here's a start, if I'm understanding correctly:

G1:
=TRIM(IF(A1<>0,TEXT(A1,"dd-mmm-yyyy"),"")&"
"&IF(C1<>0,TEXT(C1,"dd-mmm-yyyy"),"")&"
"&(IF(E1<>0,TEXT(E1,"dd-mmm-yyyy")," ")))

H1:
=TRIM(IF(B1<>0,TEXT(B1,"$#,###.00"),"")&"
"&IF(D1<>0,TEXT(D1,"$#,###.00"),"")&"
"&(IF(F1<>0,TEXT(F1,"$#,###.00")," ")))

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Norman
I got the impression he Healingbear wanted to stack all the values in a list--so that all the values from Columns A, C, E were in one column, and all the values from B, D, F were in the next column while eliminating blank cells. Presumably, if a value in A5 is blank, B5 would be blank too, since the date and value seemed to be pairs
He also wants to sort the values
I haven't been able to do this using formulas--I either get duplicate values when I use OFFSET nested in an IF to skip a blank cell, or I get conflicts between nested IFs for which the wrong Test wins. All this is happening before I tried to test for more than one consecutive blank cell..

tj
 
I can't make a formula do what you want. The following will do the job, though
Select All your data in columns A and B
Go to Data|Filter|AutoFilte
Select one of the drop down arrows and choose nonblanks
Select all the visible cells in those two column
Copy and Paste to a separate worksheet (you'll bring them back in a moment)
Turn Off the AutoFilte
Repeat all these steps for Columns C and D. When You Paste, paste these cells directly below the cells you Pasted from Column A and B
Repeat these steps for Columns E and F (don't forget to turn off the AutoFilter for C and D first
When everything is in the two columns on the new sheet, Select all the cells and Choose Data|Sort. Then, Sort by the second column
Copy and Pate all the cells back to Columns G and H

The reason why I suggest you copy to a new sheet is because the AutoFilter Hides rows. If you paste next AutoFiltered information and then deactivate the AutoFilter you can sometimes get unexpected results

This method is a little clunky, but it will work and it should only take a few minutes

tj
 
I haven't seen a response in a week and was wondering if I wasn't clea
enough, or if it is too daunting a task. I'd love to hear from someon
either way...

Thanks as always,

Healingbea
 
Back
Top