Need help on creating a formula

  • Thread starter Thread starter Craig
  • Start date Start date
C

Craig

Hi,
Am looking for some help. And I do not even know if this
is possible to do with formulas. Anyway, here is the
senario.

Example Data Worksheet:
Item # Group Feet Sales
111 2 8 10
111 3 6 10
111 4 4 10
222 2 8 10
222 3 6 10
222 4 4 10

I have two data sheets. Each data sheet has two different
time periods it looks at. What I want to do is combine
the two data sheets into one worksheet tab that will show
the sales for each of the four time periods. You will
notice I have items that repeat because of the different
groups and footages there are. So on the one worksheet
that I am wanting to bring everything over on will also
have the repeating item #'s, but I need it to look up the
sales for all the different groups and footages.

I know a vlookup wont work because it only finds the first
item number it matches and brings those values over, I
need it to find all the other values for the same item
number below that match. I have heard/read about
index,match,and offset formulas but dont fully understand
them yet. Especially trying to embed them with one
another. I hope I explaind my situation to where you can
understand what I am looking for.

I appreciate any help.
Thanks

Craig
 
Frank..thanks for the response.

I have experience with pivot tables, but can a pivot table
use 2 different worksheets?
 
Hi
you said your are looking at 4 different time periods (2 per sheet).
How do you indicate whcih time period is relevant for oe dtaset?
You may copy the sheets manually into on and add a new column which
stores the specific time period

Frank
 
Hi,
Am looking for some help. And I do not even know if this
is possible to do with formulas. Anyway, here is the
senario.

Example Data Worksheet:
Item # Group Feet Sales
111 2 8 10
111 3 6 10
111 4 4 10
222 2 8 10
222 3 6 10
222 4 4 10

I have two data sheets. Each data sheet has two different
time periods it looks at. What I want to do is combine
the two data sheets into one worksheet tab that will show
the sales for each of the four time periods. You will
notice I have items that repeat because of the different
groups and footages there are. So on the one worksheet
that I am wanting to bring everything over on will also
have the repeating item #'s, but I need it to look up the
sales for all the different groups and footages.

I know a vlookup wont work because it only finds the first
item number it matches and brings those values over, I
need it to find all the other values for the same item
number below that match. I have heard/read about
index,match,and offset formulas but dont fully understand
them yet. Especially trying to embed them with one
another. I hope I explaind my situation to where you can
understand what I am looking for.

I appreciate any help.
Thanks

Craig
Click to expand...
 
Nuts! Please ignore my prior 'posting'. Hit the @#$! send key too quickly!

...
...
Example Data Worksheet:
Item # Group Feet Sales
111 2 8 10
111 3 6 10
111 4 4 10
222 2 8 10
222 3 6 10
222 4 4 10

I have two data sheets. Each data sheet has two different
time periods it looks at. What I want to do is combine
the two data sheets into one worksheet tab that will show
the sales for each of the four time periods. You will
notice I have items that repeat because of the different
groups and footages there are. So on the one worksheet
that I am wanting to bring everything over on will also
have the repeating item #'s, but I need it to look up the
sales for all the different groups and footages.

I know a vlookup wont work because it only finds the first
item number it matches and brings those values over, I
need it to find all the other values for the same item
number below that match. I have heard/read about
index,match,and offset formulas but dont fully understand
them yet. Especially trying to embed them with one
another. I hope I explaind my situation to where you can
understand what I am looking for.
Click to expand...

If all you're trying to do is combine the four tables into one, copy & paste
would be by far the fastest and simplest way to do this. Add an extra column to
identify the table from which each set of records comes. For example, in a new
worksheet, enter TableID in A1, Item # in B1, Group in C1, Feet in D1 and Sales
in E1. Enter 1 in A2, switch to the first data worksheet and select all of table
1 *EXCEPT* the top row of field names, switch back to the new worksheet, select
cell B2 and paste. Fill A2 down to match the table 1 entries. In the cell
immediately below that in col A, enter 2, then repeat the copy & paste followed
by the col A fill. Do likewise for the other tables.

This could be done with formulas, but formulas would be both slower and more
complicated than this copy & paste procedure.
 
Back
Top