Level indicator

[hatman]

I couldn't manage without pivot tables!!!

Maybe I'm doing something wrong but I tried summarising some pretty extensive data (50,000 employees worth of pay data for a year) on Monday using SUMPRODUCT and the formula took all afternoon to calculate for just one month (actually I went home and left it going so not sure how long it took).

I then had a moment of clarity during the night and realised how to do it using pivots and I had the other eleven months summarised an hour and a half after getting to work the following morning.

Learn them, they're legendary :wink: !!!

Yes, I see how a pivot-table is perfect for that application...

The only challenge from my personal point of view is that I would need to fabricate data that lends itself to a pivot-table structure... and that almost defeats the purpose of the lesson. I would like to add them to my arsenal, but I have yet to come across data in my daily duties where that type of data drill-down would even be remotely useful.

Just to give a sample of my most data intensive workbook:

This is a sample from a table with 328 members, representing the coded part numbers for electrical connectors on a piece of Space Station hardware:

Node_3_Electrical_Tool_Crossref_ver_1-1.xls
	A	B	C	D	E	F	G
1		ORUName	ORUPartNumber	ItemSchematicID	PartNumberofItemORDrawingWherePinoutSchematicisLocated	ConnectorID	ConnectorPartNumber
2	WPA	AAA		A107		J1	NATC00T11N98PB
3	WPA	AAA		A107		J2	NATC00T11N35PN
4	WPA	ASD		A108		J1	NATC07T15N35PN
5	WPA	CatalyticReactor	SV825455-1	0229	SV826686-1	J1	NATC00T11N35PA
6	WPA	CatalyticReactor	SV825455-1	0231	SV825503-2	J1	NATC00T11N35PN
7	WPA	CatalyticReactor	SV825455-1	1126-1	SV825457-1	J1	NATC00T11N35PN
8	WPA	CatalyticReactor	SV825455-1	7047-1	SV825455	J3	NATC00T13N35PN
Hardware

And here is a sample from one of 5 other tables on 5 sepearate sheets that have 50-250 members... this particular table represents parts numbers for Test Cables, with the part numbers for the electrical connectors on each end:

Node_3_Electrical_Tool_Crossref_ver_1-1.xls
	A	B	C	D	E	F	G
1	ConnectorID(itemside)	ConnectorPartNumber(itemside)	TestToolPartNumber	TestToolDescription	ConnectorID(out)	ConnectorPartNumber(out)	Notes(fortoolorconnector)
2	P1	D38999/26FD35SN	SV825464CT206	ReactorHealthHarness#1	P1TB	D38999/26FD35PN	Valves
3	VLV4	D38999/26FB35SA	SV825464CT207	CatReactorO2ManifoldCable	PRIG	PT06A-10-6S
4	VLV5	D38999/26FB35SN	SV825464CT207	CatReactorO2ManifoldCable	PRIG	PT06A-10-6S
5	P1	D38999/26FD35PN	SV825464CT208	CatReactorO2ManifoldCable#2	PRIG	PT06A-10-6S
6	P1	D38999/26FB98SA	SV825487CT208	GLSHarness#1	P1TB	D38999/26FB98PA	Heaters
7	P2	D38999/26FB98SN	SV825487CT209	GLSHarness#2	P2TB	D38999/26FB35PN
8	P3	D38999/26FC35SN	SV825487CT210	GLSHarness#3	P3TB	D38999/26FC35PN	(9)rtdSignalsandLiquidSensor
9	P1	D38999/26FE35SN	SV825502CT202	WaterStorageHarness	P1-TB	D38999/26FE35PN	Don'tUseonWasteWaterORU
Cable

Now, how would a pivot table tell me that the test tool listed in row 3 of table 2 is one of several tools in that table that will connect with the hardware listed in row 5 of table 1? Once I know that I need to find all tools that have a D38999/26FB35SA connector listed in column B or F, an autofilter is almost the right tool... except that I can't get the union of matches in both columns. I ended up doing this with a whole slew of Match() and Index() formulas, that look like this: =MATCH(B3,INDIRECT($C$3&"!B1"):INDIRECT($C$3&"!B"&$A$1),0) and =MATCH($B$3,INDIRECT($C$3&"!F1"):INDIRECT($C$3&"!F"&$A$1),0) so I can obtain matches in BOTH columns, where C3 contains the name of the sheet (and yes, a year later, I DO, in fact see how I could make these formulas a little bit more compact, but I just don't feel like taking the time to make the changes in all 50 rows by 15 columns in each of 7 sheets, even though I might see a noticeable gain in calculation speed). I defy you to demonstrate how a Pivot Table would be more applicable in this situation.

I grant you that pivot tables are amazingly powerful for drill-down of large data sets... but I guess I'm still stuck on the fact that if you don't need to do data drill downs, and/or your data sets are TINY, a pivot-table is at best too big of a hammer, at worst it just isn't applicable.

I do, however, appreciate your opinion, and as I said to greg: now that I have a better appreciation for what they are good for, I will be keeping pivot tables in reserve for a time when I may find them useful.

 

[gingerafro]

I agree. A pivot table won't really help there. You could use one, but it wasn't designed to solve the kind of question you are asking.

As soon as you start intensively using INDEX/MATCH and VLOOKUP, what you really want is a basic MSAccess database...

 

[hatman]

As soon as you start intensively using INDEX/MATCH and VLOOKUP, what you really want is a basic MSAccess database...

Yup. I weighed the pros and cons of an Access database when I first started building the file, realizing that Access would probably be the better way to go. In the end, I figured that I could save time by staying in an environment that I was a little bit more familiar with (Excel). At the time, I was a new employee here with my first access to Access... at my previous company, they didn;t allow anybody except IT to have access to Access because then people would have too much access to the data, and that would be bad since IT hadn't taken any measures to protect the data from corruption or damage.

Perhaps if I get brought onboard for the next big contract, someone will give me resources to develop this concept in a better environment (Access)... as it was, I built the current file in my copious amounts of free time

 

[gingerafro]

I always worry when I start building and solving things in my 'free time'. It get all pleased with myself and then bothered that I should have been playing golf / driking / watching football instead.

way to go off thread.
so to get back on...

With regards to VBA, I have always thought (as when learning any language) the skills should be broken down into Reading, Writing, Listening and Speaking. You are then graded as Basic, Conversational, Fluent. Listening and Speaking may sound a bit strange, but I'd explain them as follows.

Reading - See code and know what it does.
Writing - Write code from altering the macro recorder to going from scratch
Listening - You are told things like Event, Object and how a macro works and understand
Speaking - As per listening.

Back on thread...

 

[hatman]

I always worry when I start building and solving things in my 'free time'. It get all pleased with myself and then bothered that I should have been playing golf / driking / watching football instead.
[/qoute]

Well, my use of the phrase Copious Amounts of Free Time was an obscure reference to a Tom Lehrer song.

way to go off thread.

This is the lounge, how does one go off-topic? Isn't casual conversatin a series of tengential remarks?

so to get back on...

With regards to VBA, I have always thought (as when learning any language) the skills should be broken down into Reading, Writing, Listening and Speaking. You are then graded as Basic, Conversational, Fluent. Listening and Speaking may sound a bit strange, but I'd explain them as follows.

Reading - See code and know what it does.
Writing - Write code from altering the macro recorder to going from scratch
Listening - You are told things like Event, Object and how a macro works and understand
Speaking - As per listening.

Back on thread...

Is THAT what this thread was about?

 

[gingerafro]

The Tom Lehrer reference went whizzing me by. Best I could do was to use apostrophes.

I only berated myself for going off-topic because I wanted to bring my brilliant VBA suggestion into the conversation.

My bonfire is now just smouldering.

 

[hatman]

I did NOT intend to damp down your fire. Let it flare up, hot and bright! Your knowledge is a candle, let others be illuminated by it!

Feel better?

Seriously, if you think thread is off topic, check out the one where I asked about the MrExcel MVP designation the other day... Greg Truby threw this one off into a tail-spin on the 4th post...

 

[gingerafro]

Feel better?

much. thank you. But now instead of bringing my idea to the fore, I am going to bury it under an avalanche of posts.

now I hope you have understood my sarcasm, as unfortunately you cannot see my cheeky grin as I write.
So, to fulfil my ambition - how do you represent sarcasm and the like in the forum to let others know you are only joking?

 

[hatman]

Oh, I spotted the sarcasm immediately, which is why I responded in kind. maybe we need some new emoticons... though I am still sure what an ironic emoticon would look like... make a smiley face wearing a pieces of Chain Maille (remember, you can combine Irony and Carbony to make Steely)