Pulling Data by uniqueID

[Todss]

Hello Guys,


I was wondering if there was a way to pull corresponding data by entering a uniqueID. I want to get dates and amounts of a particular uniquiID. For example, I enter 1001 in B1 (desired Sheet) then I want all the dates and amounts to come in chronological order as showed in the desired result sheet(highlighted in yellow). I'm struggling with this for long time. Please help.


Thank you in advance.

[AliGW]

Please confirm that your Excel version is 2016 and not 365 - if the latter, then you can take advantage of the new dynamic array functions.

[alansidman]

Use Advanced Filter on the Data Tab

[Todss]

It is Microsoft office professional plus 2016.

[Todss]

Thanks Alansidman for reply
Yes. I can use advance filter but then I need to refresh it again and again. I was looking for more automatize way. Meaning, as soon as I enter uniqueID, I get desired result.
I can also use m-code if there is a way.

[alansidman]

I am not at home right now, but will set up a parameter query later today with PQ unless someone else does so first.

[Pete_UK]

You can do it with a few formulae. Put this one in D2 of the Problem sheet:

=IF(B2='Desired result'!$B$1,MAX(D$1,D1)+1,"-")

Copy down as far as you like. Then use these formulae in the cells stated on the Results sheet:

A4: =IF(ROWS($1:1)>MAX(Problem!$D:$D),"",INDEX(Problem!$A:$A,MATCH(ROWS($1:1),Problem!$D:$D,0)))

B4: =IF(ROWS($1:1)>MAX(Problem!$D:$D),"",INDEX(Problem!$C:$C,MATCH(ROWS($1:1),Problem!$D:$D,0)))

Copy these down as far as you think you will need.

Hope this helps.

Pete

[alansidman]

Since you indicated that you would accept a M-code solution, look at the attached.

[Todss]

Hi Pete_UK.
It is working perfectly. Great Solution.
Can I ask you for another help.

The whole idea for this work was to calculate XIRR for each unique ID.
Now I'm getting outflow amount/date by using Vlookup and inflow amount/date by using your formula.
Since for each uniqueID the number of inflow amount/dates varies, XIRR formula is returning with value error.
For Example, 1003 is working fine because I've dragged the formula exactly equal to number of entries for 1003. But for other uniqueID its not working.
I'm attaching same example with more entries. Hope you understand what I'm trying to say.

Thank you for previous solution. And if you could help me out with this, it will be a great help.

[Todss]

Hi alansidman
Thank you for the solution

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.