Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Consolidate Rows
The data below is an example of a much larger database. I am trying to find
a way to bring all lines of the description onto the same row as the date and amount. The problem I'm havinng is that the space between Date/Amt are of varying lengths. Thanks in advance for your help. Date Amt Desc 7/25 200,000 FUNDS TRANSFER (ADVICE 2008072500018226) SENT TO JPMORGAN CHASE / BNF=DREIER LLP OPERATING OBI= RFB=080725400069 07/25/08 09:53AM ET 7/28 3,000 AUTOMATED DEBIT NEOPOST POSTAGE CO. ID. 1942388882 080728 PPD MISC 46872331 7/29 5,000 TRNSFR 2000030910634 07/29 ONLINE TRNSFR CONFIRMATION # VY114369378 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Consolidate Rows
Assuming you have a header row, so your data starts on row 2, put this
formula in D2: =IF(A2="","",C2&IF(A3=""," "&C3,"")&IF(COUNTIF(A3:A4,"")=2," "&C4,"") &IF(COUNTIF(A3:A5,"")=3," "&C5,"")&IF(COUNTIF(A3:A6,"")=4," "&C6,"")&IF (COUNTIF(A3:A7,"")=5," "&C7,"")&IF(COUNTIF(A3:A8,"")=6," "&C8,"")) and then copy it down as far as you need to. It will bring your description lines together in one cell on the same row as the date and amount, and will cope with up to 7 description cells for any particular record (your example shows 5). You can then highlight column D, click <copy then Edit | Paste Special | Values (check) | OK then <Enter in order to fix the values. Then as you have these in date order, you can highlight all the data in these 4 columns and sort by column A. This will send your rows with no dates in to the bottom, from which it is quite easy to delete them, giving you what you want. Hope this helps. Pete On Feb 4, 10:46*pm, Eric wrote: The data below is an example of a much larger database. *I am trying to find a way to bring all lines of the description onto the same row as the date and amount. *The problem I'm havinng is that the space between Date/Amt are of varying lengths. Thanks in advance for your help. Date * *Amt * * Desc 7/25 * *200,000 * *FUNDS TRANSFER *(ADVICE 2008072500018226) * * * * * * * * SENT TO *JPMORGAN CHASE */ * * * * * * * * BNF=DREIER LLP OPERATING * * * * * * * * OBI= * * * * * * * * RFB=080725400069 07/25/08 *09:53AM ET 7/28 * *3,000 * AUTOMATED DEBIT *NEOPOST POSTAGE * * * * * * * * CO. ID. 1942388882 080728 PPD * * * * * * * * MISC 46872331 7/29 * *5,000 * TRNSFR 2000030910634 07/29 * * * * * * * * ONLINE TRNSFR CONFIRMATION # VY114369378 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need to consolidate a list into rows | Excel Discussion (Misc queries) | |||
Consolidate data from worksheet (more then 65536 rows) | Excel Discussion (Misc queries) | |||
How to open/consolidate details for multiple rows in a PivotTable | Excel Discussion (Misc queries) | |||
Consolidate rows & amounts with the same heading | Excel Discussion (Misc queries) | |||
how to consolidate large worksheets w/ 800~1000 rows by category? | Excel Worksheet Functions |