Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Automate Merge
In an bank statement (excel softcopy) the movement description excel rows
look like this: 1 RECIBO TELEFONO 2 TELEFONICA MOVILES S.A. 3 RECIBO A SU CARGO 4 CANAL SATELITE DIGITAL, S.L. 5 RECIBO/OPERACION TARJETA VISA 6 RECIBO/OPERACION TARJETA VISA 7 TRASPASO O TRANSFERENCIA 8 PARQUE HARO Y AZUL, S.A. To make each row meaningful and complete I use the excel (2002) alignment features to make it look like this: 1 RECIBO TELEFONO TELEFONICA MOVILES S.A. 3 RECIBO A SU CARGO CANAL SATELITE DIGITAL, S.L. 5 RECIBO/OPERACION TARJETA VISA 6 RECIBO/OPERACION TARJETA VISA 7 TRASPASO O TRANSFERENCIA PARQUE HARO Y AZUL, S.A. I do this to prepare the excel spread sheet for import to an access database. From reading the various posts I believe that this could be automated however with my limited skills I feel that I need some direct guidence. You can see that I merged rows 1 and 2, rows 3 and 4, rows 7 and 8. I didnĀ“t need to do anything with rows 5 and 6. There is typically more than 200 mixed rows like this per month with more than 50% needing alignment. I hope that this example is clear and I look forward getting some help. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Automate Merge
Hi John
In my opinion you can only merge automatically if there is a pattern that excel can recognize in order to know which rows have to be merged. I don't know exactly what you mean by "alignment features". Never heard of them....that doesn't mean they don't exist though! You could try and record the steps you take. Then, by posting the code we could figure something out for sure :) Cheers Carlo On Feb 1, 5:12*am, John wrote: In an bank statement (excel softcopy) the movement description excel rows look like this: 1 RECIBO TELEFONO * * * * * * * 2 TELEFONICA MOVILES S.A. * * * * * * * 3 RECIBO A SU CARGO * * 4 CANAL SATELITE DIGITAL, S.L. * * * * * 5 RECIBO/OPERACION TARJETA VISA 6 RECIBO/OPERACION TARJETA VISA 7 TRASPASO O TRANSFERENCIA * * * 8 PARQUE HARO Y AZUL, S.A. * * * * * * * To make each row meaningful and complete I use the excel (2002) alignment features to make it look like this: 1 RECIBO TELEFONO TELEFONICA MOVILES S.A. * * * * * * * 3 RECIBO A SU CARGO CANAL SATELITE DIGITAL, S.L. * * * * * * * * 5 RECIBO/OPERACION TARJETA VISA 6 RECIBO/OPERACION TARJETA VISA 7 TRASPASO O TRANSFERENCIA PARQUE HARO Y AZUL, S.A. I do this to prepare the excel spread sheet for import to an access database. From reading the various posts I believe that this could be automated however with my limited skills I feel that I need some direct guidence. You can see that I merged rows 1 and 2, rows 3 and 4, rows 7 and 8. I didn“t need to do anything with rows 5 and 6. There is typically more than 200 mixed rows like this per month with more than 50% needing alignment. I hope that this example is clear and I look forward getting some help. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Automate Merge
Hi Carlo,
Three things - Firstly, Thank you for responding. Secondly, Ignore my comment "alignment features" my apologies I must have been dreaming. Thirdly, your comments and hindsight have prompted me to send a better example of the original problem (see below). I simply copied rows under Concepto 2, 4, 8 into rows 1, 3, 7 respectively. I then deleted rows 1,3 and 7 to get the desired result. Fecha OperaciĆ³n Fecha Valor Concepto 1 02-07-2007 02-07-2007 RECIBO TELEFONO 2 TELEFONICA MOVILES S.A. 3 02-07-2007 02-07-2007 RECIBO A SU CARGO 4 CANAL SATELITE DIGITAL, S.L. 5 02-07-2007 01-07-2007 RECIBO/OPERACION TARJETA VISA 6 02-07-2007 01-07-2007 RECIBO/OPERACION TARJETA VISA 7 02-07-2007 02-07-2007 TRASPASO O TRANSFERENCIA 8 PARQUE HARO Y AZUL, S.A. With regard to your comment that excel needs to recognize a pattern the only thing that I can see is the fact that there is never a date in the rows that I cut, paste and subsequently delete. I hope this clarifies the situation sufficiently. Saludos John "carlo" wrote: Hi John In my opinion you can only merge automatically if there is a pattern that excel can recognize in order to know which rows have to be merged. I don't know exactly what you mean by "alignment features". Never heard of them....that doesn't mean they don't exist though! You could try and record the steps you take. Then, by posting the code we could figure something out for sure :) Cheers Carlo On Feb 1, 5:12 am, John wrote: In an bank statement (excel softcopy) the movement description excel rows look like this: 1 RECIBO TELEFONO 2 TELEFONICA MOVILES S.A. 3 RECIBO A SU CARGO 4 CANAL SATELITE DIGITAL, S.L. 5 RECIBO/OPERACION TARJETA VISA 6 RECIBO/OPERACION TARJETA VISA 7 TRASPASO O TRANSFERENCIA 8 PARQUE HARO Y AZUL, S.A. To make each row meaningful and complete I use the excel (2002) alignment features to make it look like this: 1 RECIBO TELEFONO TELEFONICA MOVILES S.A. 3 RECIBO A SU CARGO CANAL SATELITE DIGITAL, S.L. 5 RECIBO/OPERACION TARJETA VISA 6 RECIBO/OPERACION TARJETA VISA 7 TRASPASO O TRANSFERENCIA PARQUE HARO Y AZUL, S.A. I do this to prepare the excel spread sheet for import to an access database. From reading the various posts I believe that this could be automated however with my limited skills I feel that I need some direct guidence. You can see that I merged rows 1 and 2, rows 3 and 4, rows 7 and 8. I didnĀ“t need to do anything with rows 5 and 6. There is typically more than 200 mixed rows like this per month with more than 50% needing alignment. I hope that this example is clear and I look forward getting some help. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Automate Merge
One simple formulas play to tinker with, John ..
Illustrated in this sample: http://www.freefilehosting.net/download/3bb2k Automatic Merge.xls Assume source data in cols A to D, data from row2 down Let's make use of say, the "Fecha Valor" col in col C Enter something in the cell just below the last data row, eg enter in C10: end Then put in E2: =IF(C3="",D2&" "&D3,"") in F2: =IF(AND(C2<"",E2=""),D2,E2) Select E2:F2, copy down to the last row of source data. Col F returns the desired results, which you can freeze using an "in-place" copy n paste special as values -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Automate Merge
Hi John
thanks for sending a more detailed explanation of your problem. Max's solution looks good, although it doesn't delete your lines. Following VBA Code should do what you need: Sub CleanUp() Dim SH As Worksheet Dim Col As Integer Set SH = Worksheets("Sheet1") Col = 3 For i = SH.Cells(65536, Col).End(xlUp).Row To 2 Step -1 If SH.Cells(i, 1) = "" Then SH.Cells(i - 1, Col).Value = _ SH.Cells(i - 1, Col).Value & SH.Cells(i, Col).Value SH.Rows(i).Delete End If Next i End Sub You have to adjust SH and Col according to your sheet. I used column one for checking if the line needs to be deleted, hope that is ok, otherwise you need to change that as well. I hope you understand what it does, otherwise just ask. hth Carlo |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Automate Merge
Max/Carlo,
Max the first formulae worked fine but no matter what I did I could not get the second to work. I ended up changing it to: =IF(AND(C2<"";D2<"");D2&" "&E2;E2). This seems to work but up to now I donĀ“t understand how. (I have learnt a lot in the process though). I am still playing/fighting it. Carlo thank you for the additional "clean up" code. I hope others are benifiting from this exercise John "carlo" wrote: Hi John thanks for sending a more detailed explanation of your problem. Max's solution looks good, although it doesn't delete your lines. Following VBA Code should do what you need: Sub CleanUp() Dim SH As Worksheet Dim Col As Integer Set SH = Worksheets("Sheet1") Col = 3 For i = SH.Cells(65536, Col).End(xlUp).Row To 2 Step -1 If SH.Cells(i, 1) = "" Then SH.Cells(i - 1, Col).Value = _ SH.Cells(i - 1, Col).Value & SH.Cells(i, Col).Value SH.Rows(i).Delete End If Next i End Sub You have to adjust SH and Col according to your sheet. I used column one for checking if the line needs to be deleted, hope that is ok, otherwise you need to change that as well. I hope you understand what it does, otherwise just ask. hth Carlo |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Automate Merge
John, if you had used this amendment in F2:
=IF(AND(C2<"";D2<"");D2&" "&E2;E2) don't you get a repeat* of the phrase in col D where it's concat'd up? Eg for F2, it returns as: RECIBO TELEFONO RECIBO TELEFONO TELEFONICA MOVILES S.A. (there's a repeat of the phrase: "RECIBO TELEFONO" from D2) *that's what I got when I plugged your amendment into the sample I attached earlier, with the delimiters changed to commas accordingly Maybe give it another try? using the earlier formula suggested in F2: =IF(AND(C2<"",E2=""),D2,E2) As demo'd in the sample, that seems to return the correct results -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "John" wrote: Max/Carlo, Max the first formulae worked fine but no matter what I did I could not get the second to work. I ended up changing it to: =IF(AND(C2<"";D2<"");D2&" "&E2;E2). This seems to work but up to now I donĀ“t understand how. (I have learnt a lot in the process though). I am still playing/fighting it. Carlo thank you for the additional "clean up" code. I hope others are benifiting from this exercise John |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Automate Merge
Max,
You are right, there is a repeat of the phrase in col D. I tried the earlier formula but I run into trouble ("invalid" in the formula arguments) when I insert comas. I know that the demo that you sent works fine the problems start when I insert the second formula onto my spreadsheet. John "Max" wrote: John, if you had used this amendment in F2: =IF(AND(C2<"";D2<"");D2&" "&E2;E2) don't you get a repeat* of the phrase in col D where it's concat'd up? Eg for F2, it returns as: RECIBO TELEFONO RECIBO TELEFONO TELEFONICA MOVILES S.A. (there's a repeat of the phrase: "RECIBO TELEFONO" from D2) *that's what I got when I plugged your amendment into the sample I attached earlier, with the delimiters changed to commas accordingly Maybe give it another try? using the earlier formula suggested in F2: =IF(AND(C2<"",E2=""),D2,E2) As demo'd in the sample, that seems to return the correct results -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "John" wrote: Max/Carlo, Max the first formulae worked fine but no matter what I did I could not get the second to work. I ended up changing it to: =IF(AND(C2<"";D2<"");D2&" "&E2;E2). This seems to work but up to now I donĀ“t understand how. (I have learnt a lot in the process though). I am still playing/fighting it. Carlo thank you for the additional "clean up" code. I hope others are benifiting from this exercise John |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Automate Merge
John,
.. when I insert comas. Try it like this for the 2nd formula over there, with semicolons replacing the commas: In F2: =IF(AND(C2<"";E2="");D2;E2) If the above still doesn't resolve it, perhaps you could post a link to your sample? You could use: http://www.freefilehosting.net/ to upload your sample, then copy n paste the "Direct Link" generated -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "John" wrote: Max, You are right, there is a repeat of the phrase in col D. I tried the earlier formula but I run into trouble ("invalid" in the formula arguments) when I insert comas. I know that the demo that you sent works fine the problems start when I insert the second formula onto my spreadsheet. John |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Automate Merge
Max,
Direct Link: http://www.freefilehosting.net/download/3bed9 Let me know when you get the file. John "Max" wrote: John, .. when I insert comas. Try it like this for the 2nd formula over there, with semicolons replacing the commas: In F2: =IF(AND(C2<"";E2="");D2;E2) If the above still doesn't resolve it, perhaps you could post a link to your sample? You could use: http://www.freefilehosting.net/ to upload your sample, then copy n paste the "Direct Link" generated -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "John" wrote: Max, You are right, there is a repeat of the phrase in col D. I tried the earlier formula but I run into trouble ("invalid" in the formula arguments) when I insert comas. I know that the demo that you sent works fine the problems start when I insert the second formula onto my spreadsheet. John |
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Automate Merge
You inadvertently introduced an error for the formulas in col E
In E2 down, you had: =IF(C3="",D2&" "&D3," ") It should have been in E2 down: =IF(C3="",D2&" "&D3,"") The false return should be a null string: "", not a single space Once you correct the above, col F will return correctly -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "John" wrote: Max, Direct Link: http://www.freefilehosting.net/download/3bed9 Let me know when you get the file. John |
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Automate Merge
Hi Max,
What a trip, I feel relieved and a bit embarresed at the same time. Thank you for sorting it out and another thank you for your patience!! I have just used it on 2245 bank movements (a years worth) so you can see that this this is an excellent tool. i.e hours of work in a second or two. All of the best John "Max" wrote: You inadvertently introduced an error for the formulas in col E In E2 down, you had: =IF(C3="",D2&" "&D3," ") It should have been in E2 down: =IF(C3="",D2&" "&D3,"") The false return should be a null string: "", not a single space Once you correct the above, col F will return correctly -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "John" wrote: Max, Direct Link: http://www.freefilehosting.net/download/3bed9 Let me know when you get the file. John |
#13
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Automate Merge
Welcome, John. Glad it helped and to hear that you got it going fine & swift
over there. Nothing to be embarassed about, it happens to me, too. Cheers. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "John" wrote in message ... Hi Max, What a trip, I feel relieved and a bit embarresed at the same time. Thank you for sorting it out and another thank you for your patience!! I have just used it on 2245 bank movements (a years worth) so you can see that this this is an excellent tool. i.e hours of work in a second or two. All of the best John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trying to merge from excel to word. New names won't merge | Excel Worksheet Functions | |||
Merge option is not available. How to undo merge in this case? | Excel Discussion (Misc queries) | |||
mail merge excludes my headers and critical data in Word merge | Excel Discussion (Misc queries) | |||
Merge =( formula should retain fraction type numbers after merge. | Excel Worksheet Functions | |||
how do i get my mail merge to update the data source at each merge | Excel Discussion (Misc queries) |