A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Lookup with multiple results



 
 
Thread Tools Display Modes
  #11  
Old January 25th, 2004, 07:11 PM
Ragdyer
external usenet poster
 
Posts: n/a
Default 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  
Old January 25th, 2004, 10:29 PM
Bill
external usenet poster
 
Posts: n/a
Default 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  
Old January 26th, 2004, 12:52 AM
Peo Sjoblom
external usenet poster
 
Posts: n/a
Default 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  
Old January 26th, 2004, 03:10 AM
Ragdyer
external usenet poster
 
Posts: n/a
Default 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  
Old January 26th, 2004, 04:24 PM
Bill
external usenet poster
 
Posts: n/a
Default 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  
Old January 26th, 2004, 06:19 PM
RagDyer
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:46 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.