Data Matching Question

[Jerry J.]

I have a formating issue that has me stumped. I am sure
there is a simple answer but I am just not seeing it. I
have an ACCT# column (A) and an Amount column (D) there
are various date entries with amounts and then an amount
total for the specific ACCT#. The ACCT# only appears on
the first line of the first date entry it does not fill
down to the amount total line. My dilemma is trying to
match the ACCT# to the Amount total. The worksheet
changes each time I import the data in. I would like to
be able to match the ACCT# with the amount total and move
that information to another worksheet. Any suggestions
would be greatly appreciated. Thanks alot!

 

[Laura]

Jerry

If I've understood you correctly and I've made the
assumption (I hope this is right if not it's a bit more
tricky) that the number of lines between each account and
amount total is consistant, if so try this. You'll have to
change the numbers for the number of lines that you have
between the account name in ColA and the total in ColD

Hope this helps. if the number of lines is inconsistant
let us know and i'll have a think. There might be easier
ways but I've used code like this in the past
Cheers
Laura

Sub acct_amount()

Sheets("sheet1").Activate
Rownumber = 1
Dataset = 1
Do
If Cells(Rownumber, 1) <> "" Then
Cells(Rownumber, 1).Copy _
Destination:=Sheets("Sheet2").Cells(Dataset, 1)
Cells(Rownumber + 5, 4).Copy _
Destination:=Sheets("Sheet2").Cells(Dataset, 2)
'if however you have a formula in the total cell you'll
have to use
' Cells(Rownumber + 5, 4).Copy
' Sheets("Sheet2").Cells(Dataset, 2).PasteSpecial
(xlPasteValues)
Rownumber = Rownumber + 6
Dataset = Dataset + 1
End If
Loop Until Cells(Rownumber, 1) = ""

'Rownumber = Rownumber - 1

End Sub

 

[Jerry J.]

Thank you for your post Laura. The number of lines
between ColA and ColD are not consistent. With each
import of new data the line count will change as well.
There is no formula in the total column. I am just
importing a report from a unix based system. It comes
into Excel as a simple text file. I am using Excel 2002.
I am just trying to match the account number to the
account total column and then copy that out to another
worksheet. There are various account numbers with
associated totals. It looks like this:

Acct# Description Date Jrnl# Amt
4004 Product Sales 03/25 AR52 400.00cr
03/25 AR53 50.00cr
03/25 AR54 100.00cr
03/25 AR55 35.00cr
Account Total: 500.85

4700 Sales Supplies 03/25 AR25 25.00cr
03/25 AR26 25.00cr
Account Total: 100.00

....and so on. I hope this explains my deliemma a little
more consise. Thanks again for your help.

Jerry

 

[Dick Kusleika]

Jerry

Are those blank line between the acct data? Do you want a macro that lists
all of the accounts on another sheet and their corresponding totals? Or,
are the account numbers static and already on the other sheet and you just
want to match the total up with the number?

 

[Laura]

Jerry

This should work- I've based it on the assumption that the
data has only one row between each set of data only and
this row is blank. Hope this is okay

Sub acct_amount()
Sheets("sheet1").Activate
Rownumber = 2
Dataset = 2
SetStart = 2
Do
Rownumber = Rownumber + 1
If Cells(Rownumber, 5) = "" Then
Rownumber = Rownumber - 1
Cells(SetStart, 1).Copy _
Destination:=Sheets("Sheet2").Cells(Dataset, 1)
Cells(Rownumber, 5).Copy _
Destination:=Sheets("Sheet2").Cells(Dataset, 2)
Rownumber = Rownumber + 2
SetStart = Rownumber
Dataset = Dataset + 1
End If
Loop While Cells(Rownumber, 5) <> ""
End Sub

 

[Jerry J.]

Thank you for your post. Yes those are blank lines from
one account number down to the next number in ColA. Yes I
am looking for a macro that will list all the accounts on
another sheet and their corresponding totals. In ColC the
word Account appears on the account total row after I
parse the data for Excel during import. I was thinking
this could be used as an anchor to pull the corresponding
account total cell. Like if the word account appears in
ColC then copy the value in ColE with the corresponing
cell and at the same time go back and copy and the
account number in ColA. I am sure there is code that will
perform this function but I can't put it together to do
it.

 

[Jerry J.]

Thanks again for your post Laura. Your code works great
it copies the data I need however, it copies the first
non empty cell above the account total. I have tried
tweaking it a little but I still get the first non empty
value of the cell above the account total. In addition,
it does not copy the first account number in ColA, it
brings the total amount but not the account number. If
you could help me on that I think I've got it. I will
have to figure out how to format the report to put only
one blank row between the account total and the next
account.

 

[Dick Kusleika]

Jerry

Try this

Sub MakeAcctList()

Dim cell As Range
Dim ImpSh As Worksheet
Dim DestSh As Worksheet

Set ImpSh = ThisWorkbook.Sheets(1)
Set DestSh = ThisWorkbook.Sheets(2)

For Each cell In Intersect(ImpSh.UsedRange, ImpSh.Range("A2:A65536")).Cells
If Not IsEmpty(cell) Then
With DestSh.Range("A65536").End(xlUp).Offset(1, 0)
.Value = cell.Value
.Offset(0, 1).Value = cell.End(xlToRight).End(xlDown).Value
End With
End If
Next cell

End Sub