Returning data for Duplicates in correct order

[ClickingNoise]

Hi Everyone,
Simple question & can supply a test document if needed...but hopefully a description should be adequate.
Column A...contains an ID for a task - appearing multiple times as you descend the column.
Column B...a corresponding date at which each task has appeared or re-appeared - but which lookups to another worksheet where the data has been pulled from another source with successive columns containing manually-entered information.
Using a standard vlookup obviously only pulls through the first date for each ID...I need each successive appearance to have it's appropriate date.
(in columns c,d,e,f etc. I also have to pull data from the other sheet - again only pertaining to that particular instance of the ID occuring).
As ever,any help appreciated

[TMS]

post the sample workbook please.

[ClickingNoise]

I've added a file to make it a little clearer - thanks for responding.

[TMS]

Use the following Array Formula:

Please Login or Register  to view this content.

committed with Ctrl-Shift-Enter rather than just Enter.

It should look like this in the formula bar if it has been entered correctly:

Please Login or Register  to view this content.

You could just use columns A and B but, in your test data, this does not return unique entries; A, B and C does.


Regards, TMS

[ClickingNoise]

Hi - thanks for your solution...I'm getting a circular reference error returned when I enter the array formula...?

[TMS]

Please post the workbook with the CR. Thanks

[TMS]

I put the formula in cell G2 and in cell N2 (in sheet 2). Press Ctrl-Shift-Enter to commit. Then copy down.


Regards, TMS

[ClickingNoise]

I think I've not perhaps described what I'm after too clearly...
Column A is all I have in Sheet 1 - Column B is looking for data in Sheet 2...then Columns C,D,E etc - all looking in Sheet 2.
I'm therefore looking for an array formula to enter in cell B2 on Sheet 1 - to copy down to F21.
Sorry if this wasn't clear.

[TMS]

OK, I misunderstood.

See if this helps. Note that the IDs on sheet2 need to be grouped together.


Regards, TMS

[ClickingNoise]

Thanks TMS - works beautifully...
...but raises the question(just out of interest) - can it be done with no alteration to the second sheet i.e. no grouping of IDs?

[TMS]

can it be done with no alteration to the second sheet i.e. no grouping of IDs?

I suspect not.

If you add a helper column on both sheets, then you can use a COUNTIF formula to add an "index" to the records. You can then use that index to match individual records. You might get away with just a helper column on Sheet2 and build the counter into the formula on Sheet1.

But, either way, you have to alter Sheet 2.

However, have a look at the reworked example, with and without a helper column on both sheets.

The downside of adding the helper column or using a modified formula on Sheet1 is that they require Array Formulae. Consequently, if you have lots of rows and columns, it could get kind of slow. That is/was the advantage of the method originally demonstrated ... they are normal, if a little convoluted, formulae.


Regards, TMS

[ClickingNoise]

Multiple solutions and some excellent advice.
Very much appreciated.
Regards again.

[TMS]

You're welcome. Thanks for the rep.