Automate Merge

  • Thread starter Thread starter John
  • Start date Start date
J

John

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

---
 
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
 
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
 
John, if you had used this amendment in F2:
=IF(AND(C2<>"";D2<>"");D2&" "&E2;E2)
Click to expand...
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,
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
 
John,
.. when I insert comas.
Click to expand...

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

---
 
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
 
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
 
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.
 
Back
Top