Help with Formula to pull data from one worksheet to another

[desibabuji]

Hello,

I tried searching for it but couldnt find anything. Can you please help me?

I have some data in the format

code name task length start finish status
100 John Prepare Bill 1 10/1/2012 10/2/2012
200 Jill Finalize Bill 1 10/2/2012 10/3/2012
300 Jane Mail Bill 1 10/3/2012 10/4/2012
200 John, Jill Confirm Collections 3 10/5/2012 10/8/2012
200 Jane, John Verify Collections 3 10/8/2012 10/11/2012
300 Adam Prepare Invoices 1 10/15/2012 10/16/2012
400 John Plan Next Month 4 10/20/2012 10/24/2012
400 Jane Plan Current Month 4 11/1/2012 11/4/2012
400 Adam, Jane Update Overall Plan 2 11/5/2012 11/7/2012

What I want to do is, to be able to pull all the data related to each code in a separate worksheets ! e.g. All the rows related to 200 in a separate worksheet, as

code name task length start finish status
200 Jill Finalize Bill 1 10/2/2012 10/3/2012
200 John, Jill Confirm Collections 3 10/5/2012 10/8/2012
200 Jane, John Verify Collections 3 10/8/2012 10/11/2012

and so on and so forth for all the codes.

Can I achieve this by index and match? Any pointers/help that I can get will be really appreciated. Also if this is not the correct forum, mods please move this thread or let me know and I will move it or open a new one. I was thinking that I can do it using a formula that's why I posted in this forum.

[Fotis1991]

Hi and welcome to the forum.

In H1, put your code.

In A2 and copy down and across, put this Array formula(C+Sh+En). Not just Enter.

Please Login or Register  to view this content.

Done. ?

[desibabuji]

Hi Fotis,

Thank you, Thank You, Thank You I cant explain how big a help this is.

If you dont mind can I also ask you to help me crack the second part of this puzzle now. Sorry to be so greedy and to ask for your help. I have been trying to crack to solve it but no luck so far.

In the sample sheet attached, I have added another worksheet "Totals". What I want to do is consolidate the data from "results" sheet and the "totals" sheet, to get the data displayed in "consolidated".

Essentially the code and name columns are the keys and then based on the last 4 digit of code from results and totals, I want to be able to merge/combine the data to create a consolidated worksheet.

I am so confused and clueless, dont even know if it can be done or not !! So I will really appreciate any help !! I have been reading the solutions on this forum and its amazing to see such valuable information around excel !! I think I am hooked here now

[Fotis1991]

Your new request is complicate and difficult(At least for me!)

I think i did something for you.

I'll see your answer tomorrow morning...

[desibabuji]

You Sir, Are a rockstar. Thanks for all your help. And sorry for the delay in getting back to you. I was out and travelling.