help NEEDED URGENTLY (I HAVE TRIED SOME SUGGESTIONS BUT THEY DONT WORK UNLESS I AM DOING THEM WRONG

G

gordo

Can someone please help
I have the following workbook with a selection of the data below:


INUMBR ILONGD WHHAND WHCOMM WHPEND WHSLOT01
175552 WHIRLPOOL AWM1404/4 1400RPM WASHER (AAB,5KG) 154 18
0 R4015
247294 BOSCH WFL2067 1000RPM WM(CLASIXX,A+AC,6) 111 2
0 R2023
247294 BOSCH WFL2067 1000RPM WM(CLASIXX,A+AC,6) 111 2
0 R4007
247294 BOSCH WFL2067 1000RPM WM(CLASIXX,A+AC,6) 111 2
0 R4018
247308 BOSCH WFO2467 1200RPM WM (CLASIXX,A+AB,6) 128 29
0 B1043
What i am trying to do is for example:
Inumber 247294 appears above 3 times with different locations , the
inumber being in A1-A1000 (Some Inumber appear more than once) and the
Locations in h1-h1000.
I have a seperate workbook with a list of Inumbr which is only
displayed once. What i want to do is bring back all the locations for
the Inumber.
For example : 247294 has three locations R2023,r4007,r4018. Using
vlookup only brings back the first location (r2023)and dosent allow the

second value and third respectively.
Is there any way of displaying these.
Note =cell is not an option (workbook changes)

cAN SOMEONE PLEASE EMAIL ME AT (e-mail address removed) AND I WILL
SEND THE DOC IF REQUIRED
Many thanks


Gordon
 
R

Ron Rosenfeld

One thing you can do is to keep everything in the same thread. By continually
starting new threads, you discourage those who have thought about your problem
from following up with you. In addition, if you've tried one of the previous
suggestions, it would be useful to know what problems you've had with
previously recommended solutions.

This is the third new thread you've started with this same problem.

You did not respond at all to the suggestions in your first thread.
You stated you got a #VALUE! error to the suggestion in the second thread.
And now you've started a third thread which may have, at least some, people
starting from square one, without benefit of knowing what has worked and what
has not.



Can someone please help
I have the following workbook with a selection of the data below:


INUMBR ILONGD WHHAND WHCOMM WHPEND WHSLOT01
175552 WHIRLPOOL AWM1404/4 1400RPM WASHER (AAB,5KG) 154 18
0 R4015
247294 BOSCH WFL2067 1000RPM WM(CLASIXX,A+AC,6) 111 2
0 R2023
247294 BOSCH WFL2067 1000RPM WM(CLASIXX,A+AC,6) 111 2
0 R4007
247294 BOSCH WFL2067 1000RPM WM(CLASIXX,A+AC,6) 111 2
0 R4018
247308 BOSCH WFO2467 1200RPM WM (CLASIXX,A+AB,6) 128 29
0 B1043
What i am trying to do is for example:
Inumber 247294 appears above 3 times with different locations , the
inumber being in A1-A1000 (Some Inumber appear more than once) and the
Locations in h1-h1000.
I have a seperate workbook with a list of Inumbr which is only
displayed once. What i want to do is bring back all the locations for
the Inumber.
For example : 247294 has three locations R2023,r4007,r4018. Using
vlookup only brings back the first location (r2023)and dosent allow the

second value and third respectively.
Is there any way of displaying these.
Note =cell is not an option (workbook changes)

cAN SOMEONE PLEASE EMAIL ME AT (e-mail address removed) AND I WILL
SEND THE DOC IF REQUIRED
Many thanks


Gordon
Click to expand...

--ron
 
G

gordo

My appologies. i have tried the formula below which has some success i
still have a problem when the SKU dosent match it is bringing the next
location down even when this is attached to a different SKU.
=IF(ISERROR(INDEX(Backup!$C$2:$H$3000,SMALL(IF($A4=Backup!$C$2:$C$3000,ROW(Backup!$H$2:$H$3000)),ROW($1:$1)),1)),"",INDEX(Backup!$H$2:$H$3000,SMALL(IF(Backup!$C$2:$C$3000=$A4,ROW(Backup!$C$2:$C$3000)),ROW($1:$1)),1))
Can you please advise
 
M

Martin P

This is how I see a solution to your problem, using a very small sample.
In cells A2 to A8 I have the numbers of which some may appear more than once.
In cell B2 I have =row(A2) and that is copied to the range B2:B8.
In cell C2 I have =SUMPRODUCT(--(A2=$A2:$A$8)) which is copied to the range
C2:C8.
Sheet 2:
Column A contains the unique numbers which you want to find.
Cells B1 to G1 contain the numbers 1 to 6.
Cell B2 contains
=SUMPRODUCT(Sheet1!$B$2:$B$8,--(Sheet1!$A$2:$A$8=Sheet2!$A2),--(B$1=Sheet1!$C$2:$C$8)) which is copied.
There will be zeroes which indicate that there are no further rows
containing the value. You could create a formula to give you a blank instead
of a zero.
 
R

Ron Rosenfeld

My appologies. i have tried the formula below which has some success i
still have a problem when the SKU dosent match it is bringing the next
location down even when this is attached to a different SKU.
=IF(ISERROR(INDEX(Backup!$C$2:$H$3000,SMALL(IF($A4=Backup!$C$2:$C$3000,ROW(Backup!$H$2:$H$3000)),ROW($1:$1)),1)),"",INDEX(Backup!$H$2:$H$3000,SMALL(IF(Backup!$C$2:$C$3000=$A4,ROW(Backup!$C$2:$C$3000)),ROW($1:$1)),1))
Can you please advise
Click to expand...

I would use, as I wrote previously, a different approach.

I am assuming now that your SKU is in Backup!$C$2:$C$3000 and your Loc's are in
Backup!$H$2:$H$3000.

For now, to see how it works, put the SKU for which you are looking, and the
instance number of the Loc you wish to match, in A1 and A2 respectively.

Then try this formula:

=INDEX(Backup!$H$2:$H$3000,LARGE((A1=Backup!$C$2:$C$3000)*
ROW(Backup!$C$2:$C$3000),COUNTIF(Backup!$C$2:$C$3000,A1)-A2+1)-1)


--ron
 
R

Ron Rosenfeld

My appologies. i have tried the formula below which has some success i
still have a problem when the SKU dosent match it is bringing the next
location down even when this is attached to a different SKU.
=IF(ISERROR(INDEX(Backup!$C$2:$H$3000,SMALL(IF($A4=Backup!$C$2:$C$3000,ROW(Backup!$H$2:$H$3000)),ROW($1:$1)),1)),"",INDEX(Backup!$H$2:$H$3000,SMALL(IF(Backup!$C$2:$C$3000=$A4,ROW(Backup!$C$2:$C$3000)),ROW($1:$1)),1))
Can you please advise
Click to expand...

I forgot to include in this post, although I mentioned in my first reply in one
of your other threads, that the formula is an *array* formula and must be
entered with <ctrl><shift><enter>. Look back at my first response if this is
confusing.


--ron
 
R

Ron Rosenfeld

many thanks . it worked

Thankyou very much
Click to expand...

You're welcome. Thank you for the feedback.

There are various ways to lay out your reporting; so if you run into trouble,
be sure to post back.


--ron
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

LOOKUP VALUES 5
Help with values 0

Top