Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find value and move to end of data in same row then move values in
Hi everyone,
I have no idea how to even make a start on this one. I have worksheets containing info exported from a database. On some rows (don't ask me how it happens !) values that should be in col H have over- run and are in col I and then all the data has shifted 1 column to the right. Col I is Gender and correct values should therefore be only F or M. At the moment I use autofilter and filter to the incorrect values then cut and paste contents of I to AN then cut and paste J:AN back to a range starting with I. Is there any way I can automate this with a macro? Many thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find value and move to end of data in same row then move values in
What type of database did the datta come from and what method was used to do
the exporting. If you have random placement of data excel macros can't help fix the spreadsheet. There may be ways of fixing the problem before you put the data in the spreadsheet, but I need more Info. "Diddy" wrote: Hi everyone, I have no idea how to even make a start on this one. I have worksheets containing info exported from a database. On some rows (don't ask me how it happens !) values that should be in col H have over- run and are in col I and then all the data has shifted 1 column to the right. Col I is Gender and correct values should therefore be only F or M. At the moment I use autofilter and filter to the incorrect values then cut and paste contents of I to AN then cut and paste J:AN back to a range starting with I. Is there any way I can automate this with a macro? Many thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find value and move to end of data in same row then move value
Hi Joel,
The database was SQL I believe but out of our control, we only receive data in CSV format. So, no go on this one, That's a shame, thought I could reduce the tedium!. Although the data is not random as such. It's always where a person has a third forename. Col G is forename and Col H is Middle name. Problem seems to occur when inputter has put two names into the Middle Name field in the DB. Thank you anyway Joel :-) "joel" wrote: What type of database did the datta come from and what method was used to do the exporting. If you have random placement of data excel macros can't help fix the spreadsheet. There may be ways of fixing the problem before you put the data in the spreadsheet, but I need more Info. "Diddy" wrote: Hi everyone, I have no idea how to even make a start on this one. I have worksheets containing info exported from a database. On some rows (don't ask me how it happens !) values that should be in col H have over- run and are in col I and then all the data has shifted 1 column to the right. Col I is Gender and correct values should therefore be only F or M. At the moment I use autofilter and filter to the incorrect values then cut and paste contents of I to AN then cut and paste J:AN back to a range starting with I. Is there any way I can automate this with a macro? Many thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find value and move to end of data in same row then move value
first I would Autofit the columns to make sure you don't have a problem.
Highlight the entire worksheet and the go to menu format - columns - autofit. If you still have a problem CSV is not bad way of going. First we need to know if the database is bad or the CSV has problems. You need to open the CSV file with Notepad and try to determine what the problems is. CSV file is a text file . The comma is used to sperate the dat so each field (a field is the data between commas) get put in its own column. Find some of the errors on the spreadhsheet and see why the data is being placed in the wrong column. If the CSV file is wrong then you have to fix the database. Sometimes the problem is the CSV files having double quotes marks in the wrong place. Let me know what you find wrong and post some of the lines from the CSV file that have errors (along with some good lines). I may be able to help "Diddy" wrote: Hi Joel, The database was SQL I believe but out of our control, we only receive data in CSV format. So, no go on this one, That's a shame, thought I could reduce the tedium!. Although the data is not random as such. It's always where a person has a third forename. Col G is forename and Col H is Middle name. Problem seems to occur when inputter has put two names into the Middle Name field in the DB. Thank you anyway Joel :-) "joel" wrote: What type of database did the datta come from and what method was used to do the exporting. If you have random placement of data excel macros can't help fix the spreadsheet. There may be ways of fixing the problem before you put the data in the spreadsheet, but I need more Info. "Diddy" wrote: Hi everyone, I have no idea how to even make a start on this one. I have worksheets containing info exported from a database. On some rows (don't ask me how it happens !) values that should be in col H have over- run and are in col I and then all the data has shifted 1 column to the right. Col I is Gender and correct values should therefore be only F or M. At the moment I use autofilter and filter to the incorrect values then cut and paste contents of I to AN then cut and paste J:AN back to a range starting with I. Is there any way I can automate this with a macro? Many thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find value and move to end of data in same row then move value
if your gender field is to the right of the middle name field that's parsing
out incorrectly, you could just do this. my example assumes gender is supposed to be in column E (5), but has ended up in column F because the middle name parsed out into too many pieces. With 100 records starting in row 1. change all those bits as needed dim R as integer, MyRg as range for R = 1 to 100 set MyRange = cells(r, 5) if MyRg < "F" and MyRg < "M" then MyRange.delete shift:=xlLeft end if next Hope that helps! "Diddy" wrote: Hi Joel, The database was SQL I believe but out of our control, we only receive data in CSV format. So, no go on this one, That's a shame, thought I could reduce the tedium!. Although the data is not random as such. It's always where a person has a third forename. Col G is forename and Col H is Middle name. Problem seems to occur when inputter has put two names into the Middle Name field in the DB. Thank you anyway Joel :-) "joel" wrote: What type of database did the datta come from and what method was used to do the exporting. If you have random placement of data excel macros can't help fix the spreadsheet. There may be ways of fixing the problem before you put the data in the spreadsheet, but I need more Info. "Diddy" wrote: Hi everyone, I have no idea how to even make a start on this one. I have worksheets containing info exported from a database. On some rows (don't ask me how it happens !) values that should be in col H have over- run and are in col I and then all the data has shifted 1 column to the right. Col I is Gender and correct values should therefore be only F or M. At the moment I use autofilter and filter to the incorrect values then cut and paste contents of I to AN then cut and paste J:AN back to a range starting with I. Is there any way I can automate this with a macro? Many thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find value and move to end of data in same row then move value
oops, I missed one when I edited shortened the variable name to MyRg. should
be: dim R as integer, MyRg as range for R = 1 to 100 set MyRg = cells(R, 8) if MyRg < "F" and MyRg < "M" then MyRange.delete shift:=xlLeft end if next Also, changed the column to "8" to match your described scenario with column "H". If it's possible for gender to be blank, you'd want to add "and MyRg < """ to the "if" line. "slarbie" wrote: if your gender field is to the right of the middle name field that's parsing out incorrectly, you could just do this. my example assumes gender is supposed to be in column E (5), but has ended up in column F because the middle name parsed out into too many pieces. With 100 records starting in row 1. change all those bits as needed dim R as integer, MyRg as range for R = 1 to 100 set MyRange = cells(r, 5) if MyRg < "F" and MyRg < "M" then MyRange.delete shift:=xlLeft end if next Hope that helps! "Diddy" wrote: Hi Joel, The database was SQL I believe but out of our control, we only receive data in CSV format. So, no go on this one, That's a shame, thought I could reduce the tedium!. Although the data is not random as such. It's always where a person has a third forename. Col G is forename and Col H is Middle name. Problem seems to occur when inputter has put two names into the Middle Name field in the DB. Thank you anyway Joel :-) "joel" wrote: What type of database did the datta come from and what method was used to do the exporting. If you have random placement of data excel macros can't help fix the spreadsheet. There may be ways of fixing the problem before you put the data in the spreadsheet, but I need more Info. "Diddy" wrote: Hi everyone, I have no idea how to even make a start on this one. I have worksheets containing info exported from a database. On some rows (don't ask me how it happens !) values that should be in col H have over- run and are in col I and then all the data has shifted 1 column to the right. Col I is Gender and correct values should therefore be only F or M. At the moment I use autofilter and filter to the incorrect values then cut and paste contents of I to AN then cut and paste J:AN back to a range starting with I. Is there any way I can automate this with a macro? Many thanks |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find value and move to end of data in same row then move value
OK, I'm a sloppy sloppy editor. So sorry! One last try.
Dim R As Integer, MyRg As Range For R = 1 To 100 Set MyRg = Cells(R, 8) If MyRg < "F" And MyRg < "M" Then MyRg.Delete Shift:=xlToLeft End If Next "slarbie" wrote: oops, I missed one when I edited shortened the variable name to MyRg. should be: dim R as integer, MyRg as range for R = 1 to 100 set MyRg = cells(R, 8) if MyRg < "F" and MyRg < "M" then MyRange.delete shift:=xlLeft end if next Also, changed the column to "8" to match your described scenario with column "H". If it's possible for gender to be blank, you'd want to add "and MyRg < """ to the "if" line. "slarbie" wrote: if your gender field is to the right of the middle name field that's parsing out incorrectly, you could just do this. my example assumes gender is supposed to be in column E (5), but has ended up in column F because the middle name parsed out into too many pieces. With 100 records starting in row 1. change all those bits as needed dim R as integer, MyRg as range for R = 1 to 100 set MyRange = cells(r, 5) if MyRg < "F" and MyRg < "M" then MyRange.delete shift:=xlLeft end if next Hope that helps! "Diddy" wrote: Hi Joel, The database was SQL I believe but out of our control, we only receive data in CSV format. So, no go on this one, That's a shame, thought I could reduce the tedium!. Although the data is not random as such. It's always where a person has a third forename. Col G is forename and Col H is Middle name. Problem seems to occur when inputter has put two names into the Middle Name field in the DB. Thank you anyway Joel :-) "joel" wrote: What type of database did the datta come from and what method was used to do the exporting. If you have random placement of data excel macros can't help fix the spreadsheet. There may be ways of fixing the problem before you put the data in the spreadsheet, but I need more Info. "Diddy" wrote: Hi everyone, I have no idea how to even make a start on this one. I have worksheets containing info exported from a database. On some rows (don't ask me how it happens !) values that should be in col H have over- run and are in col I and then all the data has shifted 1 column to the right. Col I is Gender and correct values should therefore be only F or M. At the moment I use autofilter and filter to the incorrect values then cut and paste contents of I to AN then cut and paste J:AN back to a range starting with I. Is there any way I can automate this with a macro? Many thanks |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find value and move to end of data in same row then move value
Your code should of worked itf you specify the correct column. Reading your
posting it looks like M or F should be in column I not H. I changed the code so that I put both middle names into Colunmn H with a space between the names. then delete column column I so the M or f moves from J to I. Dim R As Integer, MyRg As Range For RowCount = 1 To 100 If Range("I" & RowCount) < "F" And MyRg < "M" Then Range("H" & RowCount) = Range("H" & RowCount) & _ " " & Range("I" & RowCount) Range("I" & RowCount).Delete Shift:=xlToLeft End If Next "slarbie" wrote: OK, I'm a sloppy sloppy editor. So sorry! One last try. Dim R As Integer, MyRg As Range For R = 1 To 100 Set MyRg = Cells(R, 8) If MyRg < "F" And MyRg < "M" Then MyRg.Delete Shift:=xlToLeft End If Next "slarbie" wrote: oops, I missed one when I edited shortened the variable name to MyRg. should be: dim R as integer, MyRg as range for R = 1 to 100 set MyRg = cells(R, 8) if MyRg < "F" and MyRg < "M" then MyRange.delete shift:=xlLeft end if next Also, changed the column to "8" to match your described scenario with column "H". If it's possible for gender to be blank, you'd want to add "and MyRg < """ to the "if" line. "slarbie" wrote: if your gender field is to the right of the middle name field that's parsing out incorrectly, you could just do this. my example assumes gender is supposed to be in column E (5), but has ended up in column F because the middle name parsed out into too many pieces. With 100 records starting in row 1. change all those bits as needed dim R as integer, MyRg as range for R = 1 to 100 set MyRange = cells(r, 5) if MyRg < "F" and MyRg < "M" then MyRange.delete shift:=xlLeft end if next Hope that helps! "Diddy" wrote: Hi Joel, The database was SQL I believe but out of our control, we only receive data in CSV format. So, no go on this one, That's a shame, thought I could reduce the tedium!. Although the data is not random as such. It's always where a person has a third forename. Col G is forename and Col H is Middle name. Problem seems to occur when inputter has put two names into the Middle Name field in the DB. Thank you anyway Joel :-) "joel" wrote: What type of database did the datta come from and what method was used to do the exporting. If you have random placement of data excel macros can't help fix the spreadsheet. There may be ways of fixing the problem before you put the data in the spreadsheet, but I need more Info. "Diddy" wrote: Hi everyone, I have no idea how to even make a start on this one. I have worksheets containing info exported from a database. On some rows (don't ask me how it happens !) values that should be in col H have over- run and are in col I and then all the data has shifted 1 column to the right. Col I is Gender and correct values should therefore be only F or M. At the moment I use autofilter and filter to the incorrect values then cut and paste contents of I to AN then cut and paste J:AN back to a range starting with I. Is there any way I can automate this with a macro? Many thanks |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find value and move to end of data in same row then move value
Hi Slarbie,
Thank you, I've changed the column to 9 and extended the row range. I had to tidy range to get it to work. Used David McRitchie's Trimall. Many thanks Diddy "slarbie" wrote: OK, I'm a sloppy sloppy editor. So sorry! One last try. Dim R As Integer, MyRg As Range For R = 1 To 100 Set MyRg = Cells(R, 8) If MyRg < "F" And MyRg < "M" Then MyRg.Delete Shift:=xlToLeft End If Next "slarbie" wrote: oops, I missed one when I edited shortened the variable name to MyRg. should be: dim R as integer, MyRg as range for R = 1 to 100 set MyRg = cells(R, 8) if MyRg < "F" and MyRg < "M" then MyRange.delete shift:=xlLeft end if next Also, changed the column to "8" to match your described scenario with column "H". If it's possible for gender to be blank, you'd want to add "and MyRg < """ to the "if" line. "slarbie" wrote: if your gender field is to the right of the middle name field that's parsing out incorrectly, you could just do this. my example assumes gender is supposed to be in column E (5), but has ended up in column F because the middle name parsed out into too many pieces. With 100 records starting in row 1. change all those bits as needed dim R as integer, MyRg as range for R = 1 to 100 set MyRange = cells(r, 5) if MyRg < "F" and MyRg < "M" then MyRange.delete shift:=xlLeft end if next Hope that helps! "Diddy" wrote: Hi Joel, The database was SQL I believe but out of our control, we only receive data in CSV format. So, no go on this one, That's a shame, thought I could reduce the tedium!. Although the data is not random as such. It's always where a person has a third forename. Col G is forename and Col H is Middle name. Problem seems to occur when inputter has put two names into the Middle Name field in the DB. Thank you anyway Joel :-) "joel" wrote: What type of database did the datta come from and what method was used to do the exporting. If you have random placement of data excel macros can't help fix the spreadsheet. There may be ways of fixing the problem before you put the data in the spreadsheet, but I need more Info. "Diddy" wrote: Hi everyone, I have no idea how to even make a start on this one. I have worksheets containing info exported from a database. On some rows (don't ask me how it happens !) values that should be in col H have over- run and are in col I and then all the data has shifted 1 column to the right. Col I is Gender and correct values should therefore be only F or M. At the moment I use autofilter and filter to the incorrect values then cut and paste contents of I to AN then cut and paste J:AN back to a range starting with I. Is there any way I can automate this with a macro? Many thanks |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find value and move to end of data in same row then move value
Hi Joel,
What do I have to declare RowCount as ? Sorry I'm only a learner and so confused about variables etc. It would be great to get your code to work. Each snippet I pick up teaches me more. Many thanks Diddy "joel" wrote: Your code should of worked itf you specify the correct column. Reading your posting it looks like M or F should be in column I not H. I changed the code so that I put both middle names into Colunmn H with a space between the names. then delete column column I so the M or f moves from J to I. Dim R As Integer, MyRg As Range For RowCount = 1 To 100 If Range("I" & RowCount) < "F" And MyRg < "M" Then Range("H" & RowCount) = Range("H" & RowCount) & _ " " & Range("I" & RowCount) Range("I" & RowCount).Delete Shift:=xlToLeft End If Next "slarbie" wrote: OK, I'm a sloppy sloppy editor. So sorry! One last try. Dim R As Integer, MyRg As Range For R = 1 To 100 Set MyRg = Cells(R, 8) If MyRg < "F" And MyRg < "M" Then MyRg.Delete Shift:=xlToLeft End If Next "slarbie" wrote: oops, I missed one when I edited shortened the variable name to MyRg. should be: dim R as integer, MyRg as range for R = 1 to 100 set MyRg = cells(R, 8) if MyRg < "F" and MyRg < "M" then MyRange.delete shift:=xlLeft end if next Also, changed the column to "8" to match your described scenario with column "H". If it's possible for gender to be blank, you'd want to add "and MyRg < """ to the "if" line. "slarbie" wrote: if your gender field is to the right of the middle name field that's parsing out incorrectly, you could just do this. my example assumes gender is supposed to be in column E (5), but has ended up in column F because the middle name parsed out into too many pieces. With 100 records starting in row 1. change all those bits as needed dim R as integer, MyRg as range for R = 1 to 100 set MyRange = cells(r, 5) if MyRg < "F" and MyRg < "M" then MyRange.delete shift:=xlLeft end if next Hope that helps! "Diddy" wrote: Hi Joel, The database was SQL I believe but out of our control, we only receive data in CSV format. So, no go on this one, That's a shame, thought I could reduce the tedium!. Although the data is not random as such. It's always where a person has a third forename. Col G is forename and Col H is Middle name. Problem seems to occur when inputter has put two names into the Middle Name field in the DB. Thank you anyway Joel :-) "joel" wrote: What type of database did the datta come from and what method was used to do the exporting. If you have random placement of data excel macros can't help fix the spreadsheet. There may be ways of fixing the problem before you put the data in the spreadsheet, but I need more Info. "Diddy" wrote: Hi everyone, I have no idea how to even make a start on this one. I have worksheets containing info exported from a database. On some rows (don't ask me how it happens !) values that should be in col H have over- run and are in col I and then all the data has shifted 1 column to the right. Col I is Gender and correct values should therefore be only F or M. At the moment I use autofilter and filter to the incorrect values then cut and paste contents of I to AN then cut and paste J:AN back to a range starting with I. Is there any way I can automate this with a macro? Many thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Repost of find and move values<0 | Excel Worksheet Functions | |||
find and move values <0 | Excel Worksheet Functions | |||
Find and Move Data | Excel Discussion (Misc queries) | |||
find and move data within a column | Excel Programming | |||
macro to find numeric data & move it | Excel Programming |