RD
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
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
Are the products all different ie you list FORD twice would this be two different fords as in say two different vehicles
RD
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
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
I am guessing you have thousands of unique parts that can be fitted?
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
Why not just use a filter?
quote:
Originally posted by mkeats02
I am guessing you have thousands of unique parts that can be fitted?
quote:
Originally posted by jonrotheray
Why not just use a filter?
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
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.
I would probably use an Access database for managing this volume of data easily.
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