Look up Value in cell and move? 1

[jmbcreative]

I have a HUGE spreadsheet that I want to import to a database. The problem is the spreadsheet is not linear.
What I need to do is move the values of any cell that has the word "model" in it over to the right one and up one to make it linear. I'm pretty sure this is easy to do, yet I have limited knowledge of VB.

Any one have any solutions for this?

 

[SkipVought]

Hi,

1. Could you post an example of the data as it is?

2. Have you written any code at all? If so, please post your code, too.

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would cross the road for 2 cents, is…
Poultry in motion for a paltry amount! [/sub]

 

[jmbcreative]

No code as of yet.

Here is what my spreadsheet looks like

System Dell Serial# Memory Type
Model MemSize

As you can see the data for each system is on seperate rows. In order to import it properly into my database I need all the data to be on the same row, like such;

System Dell Model Serial MemoryType MemSize

Does that help?

 

[SkipVought]

You don't know VBA, so I'll give you a tip for accomplishing this on the sheet.

I assume that it all in one column.

Parse the entire column Data/text to columns... using FIXED WIDTH.

insert a column AFTER column B
[tt]
System Dell
Model
[/tt]
now your data spans 5 column, one of which is empty.

in colunms C enter in ROW 1
[tt]
=IF(ISBLANK($A2),B2,"")
[/tt]
copy down thru data rows.

copy this column & paste in column F

select ALL the data

COPY

Edit/Paste Special - VALUES

AutoFilter/Delete out the rows with nothing in column A.

VOLA!

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would cross the road for 2 cents, is…
Poultry in motion for a paltry amount! [/sub]

 

[jmbcreative]

Thanks for the tips, Skip.

I used the formula and it worked out.

One Question that i can't seem to find an answer to;

Is there a way to check if a cell contains a value within it and then delete or copy it? What I want to do is check if a cell contains "SN:" and if true, copy that value to another cell.

 

[SkipVought]

You can't delete & copy a cell based on a value with a formula.

However, you can with code.

with [A1]
  If .Value Like "*SN:*" then
    [B1].Value = .Value
    .ClearContents
  end if
end with

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would cross the road for 2 cents, is…
Poultry in motion for a paltry amount! [/sub]

 

[jmbcreative]

Thanks Skip!

That worked out. How do I perform this operation for the entire column?

 

[SkipVought]

We need more information.

1. Is the data in your column contiguous?

2. Relative to your column, what column should receive the data?

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
Poultry in motion to pullet for a paltry amount! [/sub]

 

[SkipVought]

Even this could be done on the sheet. A macro is usually done for stuff that you'ld do repeatedly.

In the receiving column...
[tt]
=If(IsErrorIFind("SN:",A1)),"",A1)
[/tt]
Copy down

Copy the column

Edit/Paste Special - VALUES

Turn on AutoFilter

on the source column add custom criteria...
[tt]
Contains SN:
[/tt]
all rows containing SN: are displayed. Select the values and hit the [Delete] key.


Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
Poultry in motion to pullet for a paltry amount! [/sub]

 

[jmbcreative]

That Auto Filter tool Rocks!

And all this time, I never knew it existed. man, what time that saves me. Most of the manipulation that I need can be done through this tool.

Thanks Mate!