ReMan
|
| posted on 10/5/12 at 08:31 PM |
|
|
Excel advanced help
Well advanced for me. I've just about got Pivots and Vlooks sussed, but dont Macro me!
I've got a large excel file with data like below:
Product Part Fitted Part Fitted
FORD Widget 1 Widget 2 Widget3
VW Widget 2 Widget 4
BMW Widget 5 Widget 5 Widget 5 Widget 5
VW Widget 2 Widget 4 Widget 5
BMW Widget 5 Widget 5 Widget 5
AUDI Widget 2
What I'm looking to do with the data is create a look up able version where I can look up or V-lookup a laod of Part numbers at once and return
the Product they wrer fitted too and how frequently:-
So I get;
Widget 1 FORD 3 FIAT 1
Widget 2 VW 4
Widget 3 BMW 3 FORD 2
Widget 4 FORD 2 FIAT 1
Or similar.
Give a me a clue, or some inspiration (Office 2010 BTW) Please
Colin
www.plusnine.co.uk
|
|
|
|
|
ReMan
|
| posted on 11/5/12 at 08:02 AM |
|
|
Having slept on this now
I guess in short I want to:
Do a v-look up that will return multiple values if they exist. I can prepare the date this way.
Perhaps Incan use IF-THEN?
I'll try later this morning
www.plusnine.co.uk
|
|
|
rallyingden
|
| posted on 11/5/12 at 08:20 AM |
|
|
Are the products all different ie you list FORD twice would this be two different fords as in say two different vehicles
RD 
|
|
|
Pdlewis
|
| posted on 11/5/12 at 08:49 AM |
|
|
I would flatted then top list so it was
Ford widget 1
Ford widget 2
VW widget 1
VW widget 2
then use it as a source for a pivot table
widget down the left
brand on top
and a count as the value instead of a sum
Build Photo Album
Updated 05/02/2009
|
|
|
ReMan
|
| posted on 11/5/12 at 11:06 AM |
|
|
quote:
Originally posted by rallyingden
Are the products all different ie you list FORD twice would this be two different fords as in say two different vehicles
RD
Did I illustrate this badly?
Widget 1 FORD 3 FIAT 1
Widget 2 VW 4
Widget 3 BMW 3 FORD 2
Widget 4 FORD 2 FIAT 1
This summarises that Widget1 was fitted 2 a Ford 3 times (in a year say) and to a Fiat One time
That Widget 2 was fitted to a VW 4 times
Widget 4 Ewas fitted to Ford 2 times Fiat 1 time
So Ford all I ned to pick up
Is that clearer?
www.plusnine.co.uk
|
|
|
mkeats02
|
| posted on 11/5/12 at 12:08 PM |
|
|
I am guessing you have thousands of unique parts that can be fitted?
|
|
|
dpowyslybbe
|
| posted on 11/5/12 at 12:21 PM |
|
|
Hi,
Try this, albeit I think a pivot table is the way to go but I know nothing about them. For the formulas in C13:F22 they are array formulas so after
entering them you need to press Ctrl-Shift-Enter in order to encase the formula in brackets { }.
Dom
 
Excel Query
|
|
|
jonrotheray
|
| posted on 11/5/12 at 01:57 PM |
|
|
Why not just use a filter?
|
|
|
ReMan
|
| posted on 11/5/12 at 02:16 PM |
|
|
quote:
Originally posted by mkeats02
I am guessing you have thousands of unique parts that can be fitted?
Yes- "Thousands" !
I can /do do this manually with a pivot for an item at a time trhat works fine and list correctly.
But I need to speed this up so that I can drop a list of say 100 parts into a look up and it returns the products and the occurances.
So for instance I can see that from this 100 items the ones that have only been used once on a VW and I can then choose to stop stocking it
www.plusnine.co.uk
|
|
|
ReMan
|
| posted on 11/5/12 at 02:49 PM |
|
|
quote:
Originally posted by jonrotheray
Why not just use a filter?
I can only filter for one or two items at a time
www.plusnine.co.uk
|
|
|
Minicooper
|
| posted on 11/5/12 at 02:52 PM |
|
|
If you have thousands I would probably use a macro to cycle through them, and then display them in the format required.
I'm not saying it can't be done other ways, but I find macros are very efficient and quick at doing lots of data providing they are
properly written.
If nobody comes up with an acceptable solution soon, I could look at a macro to do what you want maybe tomorrow
Cheers
David
|
|
|
Ivan
|
| posted on 11/5/12 at 02:55 PM |
|
|
Strictly speaking a spreadsheet is not ideal for this - what you need is a database with forms for filling in data and reports for summarising data.
|
|
|
mkeats02
|
| posted on 11/5/12 at 03:00 PM |
|
|
I would probably use an Access database for managing this volume of data easily.
|
|
|
ReMan
|
| posted on 11/5/12 at 04:52 PM |
|
|
quote:
Originally posted by dpowyslybbe
Hi,
Try this, albeit I think a pivot table is the way to go but I know nothing about them. For the formulas in C13:F22 they are array formulas so after
entering them you need to press Ctrl-Shift-Enter in order to encase the formula in brackets { }.
Dom
Excel Query
I think you've captured what I need here:
So I drop in my list of part numbers to analyse in as per Column A:12-22 and I get the results provided in Column H.
www.plusnine.co.uk
|
|
|
