Extract Data into a Summarized form
 

Hello Experts,
There are 6 steps needed in my question of how to pull data into a new
journal entry [Summary] tab:
1) How do you skip through the "" (blanks) and the zeros within column
[M], and extract each of the values? I have named this range as
[Values], current tab name is [Depreciation].

For each of the value that is extracted, on the corresponding row;
2) Go to column E (Debit account), copy and paste to the Summary tab.
3) Go to column F (Credit account), copy and paste to the Summary tab.
4) Go to column A (Description), copy and paste to the Summary tab.

5) In the Summary tab, inverse the extracted value from Step 3 to
represent a Credit entry.
6) Leave a blank row; then repeat all steps until no further values are
available.

The end result of the Summary tab would look like this:
Letters represent the Column. Numbers represent the steps above.

Starting at Row 5
(A) 51100 [step 2],(B) insert this function to Lookup the account code's
description
[=INDEX(Accounts!$A$1:$C$777,MATCH(A5,Accounts!$A$1 :$A$777,0),3)]
(G) 20,000 [step 1]

Row 6
(A) 18720 [step 3],(B) insert this function to Lookup the account code's
description
[=INDEX(Accounts!$A$1:$C$777,MATCH(A6,Accounts!$A$1 :$A$777,0),3)]
(G) -20,000 [step 5]

Row 7
(B) Building Depreciation for 10 years [step 4]

Row 9
(Blank Row in between). Loop all steps until no more values found within
[Depreciation] tab's [Values] range.


Your help is great appreciated.

Thanks in advance,
Ricky

*** Sent via Developersdex http://www.developersdex.com ***

Extract Data into a Summarized form
 

Hello again,
I think it's better to rephrase and go about this one step at a time. I
have Debit account codes in Column E, Credit account codes in Column F,
$values in Column M.

How do you go down the list of Column M and pick each value (skipping
the "" Blanks and zeros) of Column M, extract the Debit account code in
E and the $value in M within that row; paste it to a new tab? Then,
extract the Account code in Column F and the same $value in M; paste it
to the new tab, next row?

Keep looping until no further values in column M.

I'd really appreciate your help.

Thanks so much,
Ricky



*** Sent via Developersdex http://www.developersdex.com ***

Extract Data into a Summarized form
 

Hello,
So far, this code can hide the rows containing "0" value and blanks
within column M. However, how do you copy each Value and its
corresponding row data in column E and paste to a new tab? Next, copy
the same Value (but inverse the sign this time) and copy the row data in
column F to the new tab's next line? Insert 2 blank rows.

How do you continue to loop this copy and paste procedure until no
further values exist within the "Values" range of column M?

Dim C As Range

For Each C In Range("Values")
If Trim(C.Value) = "0" Or Trim(C.Value) = "" Then Rows(C.Row).Hidden
= True _
Else Rows(C.Row).Hidden = False
Next

Your help is greatly appreciated.

Thanks,
Ricky

*** Sent via Developersdex http://www.developersdex.com ***

Extract Data into a Summarized form
 

Hello Experts,
I can't seem to build on top of this existing code to summarized the
data into a new tab. Any takers?

Thanks in advance,
Ricky



*** Sent via Developersdex http://www.developersdex.com ***