Multiple Variables - Help

[Steph]

I've tried VLOOKUP and the INDEX and MATCH combo, but my data isn't
formatted the way it wants....

Source Data:

Location Prod Unit
1 12345 10
5 12345 15
8 12345 9
1 23456 22
7 23456 74

This goes on for 6000 rows, multiple locations, multiple prod, with
the unit number being the one I want.

Desired output:

Location Prod 12345 Prod 23456 Prod 34567 (...)
1 10 22 54
2 44 456 66
3 11 55 54
(...)

I've tried:
=INDEX(C:C,MATCH(A5&K3,A605:A6026&B605:B6026,1))
Which didn't work.


Please help!!!

 

[Frank Kabel]

Hi
if in your desired output row 1 is a heading row and
column A stores the location try the following formula in
B2 (assumption: the sheet with the data is named 'data')

=SUMPRODUCT(--('data'!$A$1:$A$6000=$A2),--('data'!
$B$1:$B$6000=B$1),'data'!$C$1:$C$1000)

and copy this formula

 

[Steph]

Frank,

I don't want to sum anything in the resulting field.

I'll explain: I have one sheet that contains a large list of locations
(store numbers), product numbers, and sales figuers. There are only
three cols in the sheet. Store, Prod, Sales. The list was generated so
each combo of store+product equals a sales total.

5 7249 13
6 7249 8
9 7249 3
10 7249 20
11 7249 20
12 7249 5
13 7249 20
15 7249 16
16 7249 15
19 7249 14
20 7249 23
21 7249 7
22 7249 7
23 7249 5
25 7249 15

There are multiple (20) products, and a total of over 400 stores. You
can see why the list would be long, one store can have anywhere from 1
- 20 products.

The list I'm trying to generate will break this out into one row for
the store, and one column for each product, with the sales data in the
right location.
Prod Prod Prod Prod
Store 756 861 7548 754
1
2
3
4
5
6
8
9
10
11
12
13

Does this help?

 

[Harlan Grove]

I don't want to sum anything in the resulting field.

....

For the sheer heck of it why don't you try Frank's formula and discover that
it *DOES* return what you say you want?

 

[Steph]

I did...and it does not work.

...

For the sheer heck of it why don't you try Frank's formula and discover that
it *DOES* return what you say you want?

 

[Frank Kabel]

Hi
what results did you get as SUMPRODUCT should work in this case

 

[Steph]

I just gave a VALUE cell.

What did work was to merge the first two cells into one =A1$B1 , and
just do a simple VLOOKUP using the same method.

I'll post the final formula later , but it worked like a charm.

 

[Steph]

After merging the two cells, I applied this to get the resulting format:

=VLOOKUP($A5&L$3,data!$A:$D,4,FALSE)

 

[hgrove]

Steph wrote...

After merging the two cells, I applied this to get the resulting
format:

=VLOOKUP($A5&L$3,data!$A:$D,4,FALSE)

Steph wrote in message ...

...

Frank's formula adjusted for your new criteria locations and the on
typo in his original (cols A and B spanned rows 1 to 6000 while col
spanned rows 1 to 1000),

=SUMPRODUCT(--('data'!$A$1:$A$6000=$A5),
--('data'!$L$1:$L$6000=L$3),'data'!$D$1:$D$6000)

can only return #VALUE! if there are error values in any of thes
cells. I think it was the typo in the col D range address that cause
the problems, and I appologize for not spotting it earlier