Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separate value in 1 column to 2 columns based on value in other co
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separate value in 1 column to 2 columns based on value in other co
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. "Versace77" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separate value in 1 column to 2 columns based on value in other co
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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separate value in 1 column to 2 columns based on value in othe
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" wrote: 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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separate value in 1 column to 2 columns based on value in othe
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" wrote: 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" wrote: 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" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separate value in 1 column to 2 columns based on value in othe
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separate value in 1 column to 2 columns based on value in othe
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separate value in 1 column to 2 columns based on value in othe
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separate value in 1 column to 2 columns based on value in othe
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separate value in 1 column to 2 columns based on value in othe
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separate value in 1 column to 2 columns based on value in othe
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 "Versace77" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding digits in a column based on data in a separate column | Excel Discussion (Misc queries) | |||
how do I separate last, first names from 1 column into 2 columns? | Excel Discussion (Misc queries) | |||
Counting based on values in two separate columns | Excel Worksheet Functions | |||
Separate address column to Five columns | Excel Worksheet Functions | |||
Adding Values Based on a Separate Column | Excel Discussion (Misc queries) |