Find value and move to end of data in same row then move values in

[Diddy]

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

 

[joel]

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]

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]

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

 

[slarbie]

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!

 

[slarbie]

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]

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

 

[joel]

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

 

[Diddy]

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

 

[Diddy]

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