If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Lookup with multiple results
As I said before, I don't know much about Pivot Tables, but I know there is
an option to double click on the data label so that you can change the manner in which it is presented. When you drag Part Number into the data field, doesn't it say SUM ? Double click on this and play around with the different options. With all my inexperience, I was able to list out your part numbers without them being totaled, and they were listed next to the Work orders also. This is definitely the way you want to go (Pivot Tables). -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Bill" wrote in message ... This is an excellent suggestion and would be the simplest solution of all - if it worked. I just tested this, however, and it seems that Excel doesn't like text in pivot tables. Even after reformatting the work order numbers to text, Excel still treated them like numbers (and added duplicates together). So then I tried using letters to represent work orders instead of numbers. The pivot table returned zeroes, so the letters were apparently just ignored. Keep thinking! |
#12
|
|||
|
|||
Lookup with multiple results
I've worked with pivot tables quite a bit and I can't get this to work. You can manipulate the data as far as having it count, sum, take the product etc. But it does not seem to work with non-numbers. I used ABC, DEF, etc. as work order "numbers" and the pivot table shows 1's if you select "count" and zeroes if you select "sum". None of the other options helps either. Finally, you cannot drag data within a pivot table unless you first convert the table via copy/paste special/values to a non- calculated table. So I'm not sure what you mean about dragging. You can manipulate the source data but not the pivot table itself (other than formatting and the other built-in options). Too bad, because you're right: the pivot table would be the way to go..... |
#13
|
|||
|
|||
Lookup with multiple results
Obviously you can only count text entries but if you drag the header with
the text entries to both the row and to the data you'll get a count -- Regards, Peo Sjoblom "Bill" wrote in message ... I've worked with pivot tables quite a bit and I can't get this to work. You can manipulate the data as far as having it count, sum, take the product etc. But it does not seem to work with non-numbers. I used ABC, DEF, etc. as work order "numbers" and the pivot table shows 1's if you select "count" and zeroes if you select "sum". None of the other options helps either. Finally, you cannot drag data within a pivot table unless you first convert the table via copy/paste special/values to a non- calculated table. So I'm not sure what you mean about dragging. You can manipulate the source data but not the pivot table itself (other than formatting and the other built-in options). Too bad, because you're right: the pivot table would be the way to go..... |
#14
|
|||
|
|||
Lookup with multiple results
Me advising someone on Pivot Tables is tantamount to the blind leading the
blind, BUT, I repeat: "With all my inexperience, I was able to list out your part numbers without them being totaled, and they were listed next to the Work orders also." I don't know if the display is good enough, but the data was there. Here is what I did. Set up test database: Labels in Row1 Part Num in Column A Work Order in Column B Data in A2:B20 Selected A1:B20 then, Data PivotTableReport Step 1 = Microsoft Excel list Next Step 2 = A1:B20 already selected Next Step 3 = Drag "PartNo" field button into Row portion of diagram, Drag "Work Ord" field button into Column portion *and* into Data portion of diagram, Next Step 4 = Existing Worksheet - then click in cell D1, Finish A list is now displayed, with the part numbers going down Column D. Only unique numbers are listed, (6 of them),even though there were 19 entries, no duplicates listed. Going across the columns, in the same row as the particular part number, are the work orders associated with that part number. Now, it may not be pretty, but it at least demonstrates that if I can do this with a Pivot Table and get these results, the proper utilization of this feature should return any and all the information that you might desire. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Bill" wrote in message ... I've worked with pivot tables quite a bit and I can't get this to work. You can manipulate the data as far as having it count, sum, take the product etc. But it does not seem to work with non-numbers. I used ABC, DEF, etc. as work order "numbers" and the pivot table shows 1's if you select "count" and zeroes if you select "sum". None of the other options helps either. Finally, you cannot drag data within a pivot table unless you first convert the table via copy/paste special/values to a non- calculated table. So I'm not sure what you mean about dragging. You can manipulate the source data but not the pivot table itself (other than formatting and the other built-in options). Too bad, because you're right: the pivot table would be the way to go..... |
#15
|
|||
|
|||
Lookup with multiple results
Well you're better than you give yourself credit for. I just tried this and it worked as advertised. I then tried it with letters instead of numbers for the work orders (just for fun) and that really does NOT work - as I've said before, it seems that the data must be numbers to be returned properly. So your idea works great for me because the work order numbers are, in fact, numbers. Thanks for your persistence! |
#16
|
|||
|
|||
Lookup with multiple results
Glad to help ... BUT,
Beside using that pot of coffee to help reading up on newsgroups, Grab another one and spend time at Debra's web site, Pivot Tables I previously gave you the link. Here it is again: http://www.contextures.com/tiptech.html -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Bill" wrote in message ... Well you're better than you give yourself credit for. I just tried this and it worked as advertised. I then tried it with letters instead of numbers for the work orders (just for fun) and that really does NOT work - as I've said before, it seems that the data must be numbers to be returned properly. So your idea works great for me because the work order numbers are, in fact, numbers. Thanks for your persistence! |
|
Thread Tools | |
Display Modes | |
|
|