J
jodi.sage
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.
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.