Separate value in 1 column to 2 columns based on value in other co

[Versace77]

Hi everyone,

Looking for help on trying to separate debits/credits that are listed in one
column (Column 2). Column 4 has either a DB or CR. I'm looking for a way to
have the values in C2 move (cut/paste) to Column 3, same row, if the value in
C4 is a CR.

Thanks as always.

Versace77

 

[Versace77]

Hi again, just writing a follow-up to my question and i think it come off
confusing. Here's an example of the data i'm working with:

$400.00 DR
$550.00 DR
$59.00 CR
$88.00 DR
$757.00 CR

I'd like to macro to move the credits over like this:

$400.00 DR
$550.00 DR
$59.00 CR
$88.00 DR
$757.00 CR

Hope this helps. thank you.

 

[Kassie]

Not at al clear what you want to do here!

have the values in C2 move (cut/paste) to Column 3 - But column C is column 3?
if the value in C4 is a CR.

Why move C2 if C4 is a credit?

I would understand if you stated htat you have a column, say C, which
contains both credits and debits, and you want to move the debits to say col
D and the credits to say Col E. The only wuestion would then be how are you
differentiating. The logical answer would be the credits are preceded by a
-. Now that's easy. Is this what you want, else be a bit more specific?


--
HTH

Kassie

Replace xxx with hotmail

 

[Versace77]

Hi Kassie, thanks for the response i can see how it could read confusingly so
i added a follow up reply which shows an example of the information that i'm
working with and what I'd like it to appear as:

$400.00 DR
$550.00 DR
$59.00 CR
$88.00 DR
$757.00 CR

I'd like to macro to move the credits over like this:

$400.00 DR
$550.00 DR
$59.00 CR
$88.00 DR
$757.00 CR

I hope that makes it easier to get my request across. the columns that i'm
working with are B, C and D but for some reason they are showing up as
numbers like the rows do and not letters, that's where i think i got confused
when trying to explain the question in the original first post.

 

[Kassie]

Let's start with your column headers. Click on Tools, Options, select the
General tab, untick R1C1reference style, and you'll be back to A, B, C.
Am I correct in believing that Col B is empty at the moment?
I would rather use formulae to do the move, rather than a macro, unless this
is something that continually happens, eg because you import data. For a
once-off, rather a formula, but let me know.

--
HTH

Kassie

Replace xxx with hotmail

 

[Versace77]

Wow, thanks, always get to learn something new when i come here regardless of
the simplicity, thanks.

Currently, column C is the empty column. Column B has the dollar amounts
and Column D has either DR or CR.

This is something that continually happens (once or twice a month). There
will be anywhere from 150 - 250 rows of data for several different people and
various charges. example, one person will have maybe 20 rows worth of data,
then there'll be a blank row and the row below that will begin charges for a
different person.

Let me know if i can provide any additional info.

thanks.

 

[Kassie]

Try this first.

Insert a new Col D.
In Col C, next to the first amount, let's say C2, insert the following formula
=IF(B2="","",IF(E2="DR",B2,""))
Next, in Col D, insert the following formula
=IF(B2="","",IF(E2="CR",B2,"")
Copy formulae down to the last row of data. You do not have to worry about
the empty rows, t will work there as well.

Unfotunately, I am on my way to go camp, that's why I don't give you a VBA
solution at this point. I'm sure this methos will already help a lot, but
when I come back, if you still need the VBA, contact me and I'll assist.

Now Copy all the rows in Cols C and D, and in sito, paste special as value.
Delete Col B, and you're done.

--
HTH

Kassie

Replace xxx with hotmail

 

[Versace77]

Thanks for the formula Kassie. I do have one question, if you still haven't
left yet, what do you mean by 'sito'? Thanks and i'll give it a try!

 

[Kassie]

In place. Iow, you copy the data you got as a result of the formulae in Cols
C and D, and paste them back in there original location.

--
HTH

Kassie

Replace xxx with hotmail

 

[Versace77]

Got it Kassie, thanks. this works great, appreciate you taking the time to
do this. Maybe i will try to have this converted to a macro later on in the
future. Your formula definitely helps to save mucho time. Thanks again and
have fun at camp!

 

[Kassie]

Glad I could help! As I say, if you contact me next week Wednesday, I'll do
the macro for you.

--
HTH

Kassie

Replace xxx with hotmail