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.

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.

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.

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
---

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

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

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

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

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

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

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

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

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