Array, Range, Lookup - I'm not sure!
 

This is a bit long, primarily because of data...

I have a utility that creates combinations of variables, based on "x"
number of variable to use in testing. Worksheet1 is the Key to
defining RxC variables from worksheet2 i.e the legend, to be place in
Worksheet3.

Placed in a work book the data for Worksheet 1 would look something
like this (330 Rows X 12 Columns) or
Columns:
A B C D E F G H I
J K L
a a a f v c a a
b b b a
b h b g n b c b a
d a b
b b a a m a b b c
a b a
a c b j c a b a
c e a b
a d a e f b c a a
c b a

On Worksheet 2 are the variables:

WorkSheet2 = 64 Rows X 12 Columns
Column A: Column B Column C Column D
Employee Phone Expense Cash USA Default
User Meetings Credit
International Default
Misc Expense
Boston
Monthly Expense
Chicago
Office Supplies
Washington DC
Other (add comments)
Denver
Project Expense
Las Vegas
Recruiting
New York

San Diego

Seattle
Worksheet3 will = 330 Rows X 12 Columns (Note Colum H will have the
largest number of data variables 64, most variables in the other
columns are 2 or 3, with a couple at 10 and 11.)

What I want to do is match the alpha character in a cell on the rows
in Worksheet 1 that correlate with the variables in worksheet2. So if
cell A1 has 'a' then Employee will be written to Worksheet 3 A1; If A2
has 'd' then Monthly Exp will be written to Worksheet 3 A2 etc...
Something like this (abbreviated for space):

Column A: Column B Column C Column D
'a' = Employee a=Ph Exp a = Cash f = Denver
'b' = User b=Meetings b = credit g = Vegas
c=Misc Exp a
= USA def
d=Monthly Exp j =
Seattle

I'm trying to do is write a macro using either arrays (single or multi)
or ranges or table lookups that will accomplish this and am finding
myself very confused. My thought is to use if/then statements nested
in a for/next loop to search, match, copy, paste the data from one
worksheet to another, but what to use?

First is it possible to do this using arrays/ranges/lookup in vba?

Which would be better, array/range/lookup? Is there a better method?

My searches here have found many examples where the data is deleted if
a duplicate is found using any of the methods already mentions, so I'm
pretty confident it's possible I'm just not proficient with these
methods unless I'm using a userform to add data or creating very small
lookup tables with arrays.

If someone could help me out here, I'd greatly appreciate those words
(examples) of wisdom and experience.

Thanks in advance.

Array, Range, Lookup - I'm not sure!
 

Jodi,

If you grouped all your data on a single sheet, your problem could easily be
solved using an INDIRECT formula. But it is probably better to have 3
seperate sheets and use a combination of the Match and Index formulas. No
VBA required.

What I can't figure out is just why you are doing this?

Cheers,

Allan P. London, CPA


wrote in message
oups.com...
This is a bit long, primarily because of data...

I have a utility that creates combinations of variables, based on "x"
number of variable to use in testing. Worksheet1 is the Key to
defining RxC variables from worksheet2 i.e the legend, to be place in
Worksheet3.

Placed in a work book the data for Worksheet 1 would look something
like this (330 Rows X 12 Columns) or
Columns:
A B C D E F G H I
J K L
a a a f v c a a
b b b a
b h b g n b c b a
d a b
b b a a m a b b c
a b a
a c b j c a b a
c e a b
a d a e f b c a a
c b a

On Worksheet 2 are the variables:

WorkSheet2 = 64 Rows X 12 Columns
Column A: Column B Column C Column D
Employee Phone Expense Cash USA Default
User Meetings Credit
International Default
Misc Expense
Boston
Monthly Expense
Chicago
Office Supplies
Washington DC
Other (add comments)
Denver
Project Expense
Las Vegas
Recruiting
New York

San Diego

Seattle
Worksheet3 will = 330 Rows X 12 Columns (Note Colum H will have the
largest number of data variables 64, most variables in the other
columns are 2 or 3, with a couple at 10 and 11.)

What I want to do is match the alpha character in a cell on the rows
in Worksheet 1 that correlate with the variables in worksheet2. So if
cell A1 has 'a' then Employee will be written to Worksheet 3 A1; If A2
has 'd' then Monthly Exp will be written to Worksheet 3 A2 etc...
Something like this (abbreviated for space):

Column A: Column B Column C Column D
'a' = Employee a=Ph Exp a = Cash f = Denver
'b' = User b=Meetings b = credit g = Vegas
c=Misc Exp a
= USA def
d=Monthly Exp j =
Seattle

I'm trying to do is write a macro using either arrays (single or multi)
or ranges or table lookups that will accomplish this and am finding
myself very confused. My thought is to use if/then statements nested
in a for/next loop to search, match, copy, paste the data from one
worksheet to another, but what to use?

First is it possible to do this using arrays/ranges/lookup in vba?

Which would be better, array/range/lookup? Is there a better method?

My searches here have found many examples where the data is deleted if
a duplicate is found using any of the methods already mentions, so I'm
pretty confident it's possible I'm just not proficient with these
methods unless I'm using a userform to add data or creating very small
lookup tables with arrays.

If someone could help me out here, I'd greatly appreciate those words
(examples) of wisdom and experience.

Thanks in advance.

Array, Range, Lookup - I'm not sure!
 

Allan,
It was a request from a co-worker who didn't want to spend the time
correlating the 330 test cases by 'hand'. My guess would be that if I
could get it to work, it would be used by other team members after the
utility is ran. And at the time it seemed a worthy challenge.

The thought of putting the data on one page hadn't occured to me, I'll
give this a try using the method you recommended.

Thank you!
Jodi

On Oct 31, 1:56 am, "alondon" wrote:
Jodi,

If you grouped all your data on a single sheet, your problem could easily be
solved using an INDIRECT formula. But it is probably better to have 3
seperate sheets and use a combination of the Match and Index formulas. No
VBA required.

What I can't figure out is just why you are doing this?

Cheers,

Allan P. London, CPA

wrote in ooglegroups.com...

This is a bit long, primarily because of data...

I have a utility that creates combinations of variables, based on "x"
number of variable to use in testing. Worksheet1 is the Key to
defining RxC variables from worksheet2 i.e the legend, to be place in
Worksheet3.

Placed in a work book the data for Worksheet 1 would look something
like this (330 Rows X 12 Columns) or
Columns:
A B C D E F G H I
J K L
a a a f v c a a
b b b a
b h b g n b c b a
d a b
b b a a m a b b c
a b a
a c b j c a b a
c e a b
a d a e f b c a a
c b a

On Worksheet 2 are the variables:

WorkSheet2 = 64 Rows X 12 Columns
Column A: Column B Column C Column D
Employee Phone Expense Cash USA Default
User Meetings Credit
International Default
Misc Expense
Boston
Monthly Expense
Chicago
Office Supplies
Washington DC
Other (add comments)
Denver
Project Expense
Las Vegas
Recruiting
New York

San Diego

Seattle
Worksheet3 will = 330 Rows X 12 Columns (Note Colum H will have the
largest number of data variables 64, most variables in the other
columns are 2 or 3, with a couple at 10 and 11.)

What I want to do is match the alpha character in a cell on the rows
in Worksheet 1 that correlate with the variables in worksheet2. So if
cell A1 has 'a' then Employee will be written to Worksheet 3 A1; If A2
has 'd' then Monthly Exp will be written to Worksheet 3 A2 etc...
Something like this (abbreviated for space):

Column A: Column B Column C Column D
'a' = Employee a=Ph Exp a = Cash f = Denver
'b' = User b=Meetings b = credit g = Vegas
c=Misc Exp a
= USA def
d=Monthly Exp j =
Seattle

I'm trying to do is write a macro using either arrays (single or multi)
or ranges or table lookups that will accomplish this and am finding
myself very confused. My thought is to use if/then statements nested
in a for/next loop to search, match, copy, paste the data from one
worksheet to another, but what to use?

First is it possible to do this using arrays/ranges/lookup in vba?

Which would be better, array/range/lookup? Is there a better method?

My searches here have found many examples where the data is deleted if
a duplicate is found using any of the methods already mentions, so I'm
pretty confident it's possible I'm just not proficient with these
methods unless I'm using a userform to add data or creating very small
lookup tables with arrays.

If someone could help me out here, I'd greatly appreciate those words
(examples) of wisdom and experience.

Thanks in advance.