Out of my depth - yet the water's still only shallow

I fear this is embarrassingly fundamental but it's stumped me all day and I'd be most grateful for some pointers.

I have two worksheets.

The first worksheet is called Jobs and has two columns.
The first column is called Order No. and is a list of numbers that are not unique.
The second column is called Job No. and is a different list of numbers which ARE unique.

My second worksheet is called Orders and also has two columns.
The first cell of the first column will contain a number copied from the Order No. column of the Jobs worksheet.
The second column is what I would like to populate automatically with data also from the Jobs worksheet.

Here's what I'd like to happen:
When I enter an Order No. into the first cell of the first column of the Orders worksheet, I want the second column of the Orders worksheet to list ALL of the unique numbers from the Job No. column of the Jobs worksheet that appear in the same rows wherever the Order No appears.

I hope I'm being clear and that you can point me towards the correct functions.

Let me know if you need more information.

Thanks.

Alecson

Good afternoon,

The way I usually go about scenarios like these is to make a helper column and then index-match. So, on the Jobs worksheet, make a flag column like :: = --Order #-- & countif(--order #--, $a$1:a1) and drag it down. What you'll end up with is each order number and the numbered occurrence of it (ABC1, DEF1, ABC2, HIK1, etc...).

Then on the order sheet you can call them with index-match. I'll assume the Order number is in a2, with a1 and b1 being headers.

In b2 : iferror(index(--Job # col--, match($a$2 & [row() -1 ***1 being the number of header rows***],---flag column---, 0 ***exact match***)),"")

In this scenario, b2 will return the first marked Job #, b3 will return the second, and so on. The iferror will return blanks whenever the formula runs out of answers (throws an error).

Hope this helps,

Good evening and thank you very much for your advice.

I've achieved a small part of it, creating the nifty countif function but I'm at a loss as how to "make a flag column". I may well be misunderstanding your instructions but I don't know how to get both the Order # and the countif function in the same cell

Kind regards

Whoah! I've made the flag column and it does exactly what you describe, bigoree09. So I've learned something new, already. Many thanks.

But I can't get the function you suggest for b2 to work. It keeps telling me the name is not valid and highlights [row()

I feel I'm on the cusp but need a little extra help to cross the summit.

Sorry use a parenthesis instead of a bracket. I used the bracket for readability.

Thank you for the clarification, although I'm not quite there, for some reason.

Here's what I've got in b2 of my Orders worksheet:

=iferror(INDEX(Jobs!B:B, MATCH($A$2 & (ROW() -1), Jobs!C:C, 0)),"")

I'm not familiar with INDEX or MATCH and am unable to figure out why it's returning #NAME?

You help is much appreciated - and I'm learning at the same tim, which is really rewarding.

Alecson said:

I'm not familiar with INDEX or MATCH and am unable to figure out why it's returning #NAME?

Click to expand...

If you're using a version of Excel prior to Excel2007, IFERROR didn't exist and will return that #NAME? error. Leave the IFERROR out altogether to begin with, then when you've got the formula right, introduce the (longer) equivalent fo Excel 2003 and earlier. Something along the lines of
=if(iserror(WorkingFormula),"",WorkingFormula)

Personally, I would consider a pivot table.

That's very perceptive, p45cal. I have a neolithic version of Excel. Thanks for the information. I'll try to get the formula working or try to lear how to use pivot tables.