Multiple List function

  • Thread starter Thread starter George
  • Start date Start date
G

George

I have one list that contains general info. I made another
to list different columns value from first list for Data,
index and/or lookup functions.

Is there a way to find the row numbers from first list
that match the value in second list to make it easier when
entering new or updating.

Example: 1st list may contain unique value as "Item No"
with another column listing "Where Used". 2nd list may
contain unique value of "Where Used" then respond with
list on Item No's in each cell.

Goal is to add or update the 2nd list to find all Where
Used from the 1st list that may not be in sort order.
 
Hi Tom,
We need to talk.
The help I am seeking goes way beyond what you explained.
Maybe my help was generally but has big meaning.

Hope to hear from u soon
Rgds,
George
 
Hi George
probably it's just too late but I don't fully understand your issue :-)
Maybe you can post some example rows for both lists and your desired
result (plain text, please - not attachments)
 
Thanks Frank,

To help explain look at this

First List is Part Master
"Part No" "Tool No" Row
164A388H01 548 12
164A388H02 548 13
164A388H03 548 14
164A388H04 548 15
164A388H05 548 16
210445 11574 17
210445 11574 18
210445 11574 19
210445 11574 20
2488A07H08 548 21
2488A07H09 548 22
2488A07H10 548 23

Second list first column is Tool No
Enter Tool No then find all part numbers associated.
DcountA shows number of tools in part list, (PN must be
present) But as you can see the row return will not work
for index or lookup's when using plus 1 to validation list.
25 PN's max per tool is the capacity of my tool list

Control for second list looks like this
"ToolNo" "PartNumber01" "PartNumber02", Etc

I can get first PN from PN list then use INDEX to find
others but the PN list is sorted for lookups which throws
the lookup or index out of order.

Both list's have index value 1 for doing this and other
systems that use this list.

Thanks Frank,

George
Ingleside IL, UUSA
 
Hi
I hope I understood you correclt. So on your second list you want
something like this
A B C D
548 164A388H01 164A388H02 ...
you will enter the value in cell A1, and the adjacent cells B1:X1 will
be filled automatically. If this is corect, enter the following formula
in B2
=INDEX('first_list'!$A$1:$A$1000,SMALL(IF('first_list'!$B$1:$B$1000=$A1
,ROW($A$1:$A$1000),10000),COLUMN()-1),0)
enter this as array formula (CTRL+SHIFT+ENTER) and copy to the
left/down.
This formula will return #REF if no more valid entries are found. If
you want to prevent this kind of error, you may use one of the
following solutions:
- use conditional formating and assign a white font for error values
- Change the above formula as follows:
=I(ISERROR(INDEX(......)),"",INDEX(....))
 
Hi Tom,
Sorry I forgot to fill in blanks.
Review my response to Frank for further details if needed.

Thanks again,
George
Ingleside IL USA
 
Hi Frank,

WOW! Major form there pal.......Thanks,
I am aware of Array's so understand your fix to my
problem. Just may have to play to get it right, but looks
like just what I need to solve this and make it easy to
add Tool No to other list.

Thank you for your help.
Please em if needs be

Regards,
George Gaulke
Ingleside, IL USA
 
Hi George
thanks for the thanks. If you have problems adapting this formula just
come back.
 
Back
Top