Excel Query

[justincase_2008]

I was given a query at work to re work into a new format. I was able to move the columns to the right order but some we still have to edit and fill in our selfs. For example Column B will always be the letter R yet i cant figure out how to set excel up to auto fill all the needed rows in that column with the letter R.

The next thing im stuck on is Column D displays the pulled product ID from our server.
Some IDs have "-BER" on the end of them showing it is a bad product. Im trying to see if there is a way to say "scan" column D for the phrase "BER" and auto cut and paste that info into column L but in the same row.


Im running Excel 2003 on a windows 2000 pc Any help you guys can give would be amazing.

[royUK]

What Query do you mean, is it from a database?

[justincase_2008]

What Query do you mean, is it from a database?

Yes a horrible sql database that looks like a 5 year old set up.

I can run the query and then copy the info into a normal worksheet.
If that will help with changing the column data.

[royUK]

can you attach an example workbook to demonstrate the layout?

[justincase_2008]

can you attach an example workbook to demonstrate the layout?

Yes give me a minute to get it. Im VNCing to the pc that can run the query.

[justincase_2008]

Here is the file. I added comments to the columns i was talking about.

Templete file.xls

[justincase_2008]

Any ideas?

[royUK]

This formula in L2 & copied down

=IF(RIGHT(D2,3)="BER",RIGHT(D2,3),"")

[contaminated]

Excel tells me "File is corrupt and cannot be opened". I'm curious to know why?!

[royUK]

It opens for me

[justincase_2008]

This formula in L2 & copied down

=IF(RIGHT(D2,3)="BER",RIGHT(D2,3),"")

How do i use this formula? I have never done anything like this in excel this is all new grounds to me so sorry for asking what should be a simple thing to do. And would i have to put anything special in the Formula since sometimes the data can go all the way down to 200-300+ product IDs?

[justincase_2008]

Excel tells me "File is corrupt and cannot be opened". I'm curious to know why?!

Im not sure i can open with my windows and my Ubuntu PCs.

[justincase_2008]

I got the Formula pasted into L2 and edited it to a row that has "-BER" in it and its not pulling the data over to L. And from what i see in the formula ill have to write out D2,3,4,5 and so on to cover all of the rows right?

=IF(RIGHT(D80,3)="BER",RIGHT(D80,3),"")

[royUK]

It works in your example sheet

[justincase_2008]

I got it all working now. I forgot to drag it all the way down. THANK YOU!!! Now the only thing i have to do is rework the formulas to place a R and 1 in the other rows. The only thing left is after i run the query i copy it and pase it into the templet file and let the formulas do there work. Then i have to copy it again and paste special (value) it to a new spread sheet to get rid of the -BER in column D without killing my Column L data. So this saves me a lot of time. THANK YOU so much for your help. If you need anything (besides excel work) im here.

[royUK]

You don't need to copy & paste, try using AutoFilter

[justincase_2008]

How would i do the auto filler?
I know i can put a R and on th lower right hand side drag that down and fill but is there a way to auto fill the column if there is data in the same row?

[justincase_2008]

nvm i just went ahead and filled the template sheet to have both rows already filler with R and the 1.

[royUK]

If you put an R in the cell, hover the cursor over the bottom right corner of the cell it will change to cross hairs(+), then double click. It will fill down.

I meant to use AutoFilter to remove unwanted rows

[justincase_2008]

If you put an R in the cell, hover the cursor over the bottom right corner of the cell it will change to cross hairs(+), then double click. It will fill down.

I meant to use AutoFilter to remove unwanted rows

all right that will have to work for him. Thanks again for all the help.

[royUK]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[justincase_2008]

Done. And thanks again.

[royUK]

Glad we helped