Help with an Excel Project (macro or formula??) 1

[zcarroll872005]

I am working on a project in excel where I have a list of information about 300 rows long and around 30 columns wide. What I’m trying to do is automate a process where excel will look in column A, identify a certain three letter symbol, and then transport the 30 columns of information in that particular row onto the next spreadsheet. There are 3 different three letter agencies within this group of information, and I need to pull the information only for two of them. This process would be easy to do in Access, but I can’t import the information without losing the color coding required within the excel cells (or can I??). Any tips on how to approach this problem? I’m not aware of what formula in excel would literally copy and paste columns upon columns of information.
Visual description:
Column A Column B Column C Column D
ABC 500 Process Over
DEF 800 Process Under
GHI 1,100 Process N/A
ABC 500 Process Under

I need a formula that will pick out ABC and GHI and post the columns of information in those rows onto the next spreadsheet.
ABC 500 Process Over
ABC 500 Process Under
GHI 1,100 Process N/A

 

[SkipVought]

Hi,

Check out the VLOOKUP() function.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[zcarroll872005]

But will the Vlookup function copy several columns of information within that row? I thought the Vlookup function only returned a single cell worth of information? I very well could be wrong.

Based on the 3 letter code in column A, I need excel to copy the next 30 columns of information as the result of that code. Will Vlookup do that??

 

[SkipVought]

One VLOOKUP for each column.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AnotherHiggins]

You'll use the formula in each of the 30 columns on the destination sheet.

Here's a tip: Refer to the entire source table in the formulas in each column, then, to tell the formula which column to return, use the Column() function.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Gavona]

Another way would be to use advanced filter to copy to a new location. As I recall this will retain existing formats/colours whereas lookups will not. You MUST use named ranges to make this work.

Alternatively use Autofilters and copy/paste.

Either way, once you have it working how you want, automate by using the macro recorder. If you need help fine tuning the results then post in forum707

but I can't import the information without losing the color coding required within the excel cells (or can I??).

Are there any rules determining the colour coding? How many different rules? Just wondering if the formula based approach could be made to work by using Conditional Formatting.

Gavin

 

[zcarroll872005]

Gavona, the filter option worked brilliantly! I guess I didn't realize the excel filter option was able to break down information as much as it could. But yeah, fixed my problem and the bosses are happy now lol

Thanks for all the help everyone! I'm sure you'll see me again on here!