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" <alon...@alondon.com.invalid> 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
>
> <jodi.s...@gmail.com> wrote in messagenews:(E-Mail Removed)...
>
> > 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.
Similar Threads
