Help Reorganising / Presenting My Data

[dvent]

Hi All,

I have attached a workbook to help.

I would like to present the data from Sheet 1 on Sheet 2 in a slightly different way.

I want to show, on Sheet 2, all of the projects in Phase A, Phase B, Phase C. So all of the projects in Phase A will be listed in Column A etc.

I'm sure this should be too hard?!

Thanks all for your help today!
dvent

[Ron Coderre]

Using your posted workbook....on Sheet2...

A3: =IF(COUNTIF(Sheet1!$H$2:$H$20,"*"&A$2)>=ROWS($3:3),
INDEX(Sheet1!$A$1:$A$20,SMALL(INDEX((RIGHT(Sheet1!$H$2:$H$20,1)=A$2)*
ROW(Sheet1!$H$2:$H$20),0),ROWS($3:3)+COUNTIF(Sheet1!$H$2:$H$20,"<>*"&A$2))),"")

Copy that formula down and across through E10.

These are the results:

A_____________B_____________C_______D_______________E
LEEDS_________MANCHESTER____WALES___LIVERPOOL_______LONDON
NEWCASTLE___________________________________________SCOTLAND
SUNDERLAND

Is that something you can work with?

[dvent]

Thanks Ron

I have tried to copy into my real spreadsheet without luck.

Can you simply explain which bits I need to change and what to look out for?

What is happening is that Column A is working but Column B - F seem to be pulling incorrect info.

Thanks
dvent

[Ron Coderre]

I attached an edited version of your workbook that contains the formula I posted.

Does that help?

[dvent]

Thats as I thought. The only thing that changes from Column A to B is the A or B i.e.

.......*"&A$2

.......*"&B$2

When I have put into my new workbook I have the following 2 formulas:

Column A:

=IF(COUNTIF(RESULTS_RR!$AO$3:$AO$40,"*"&A$2)>=ROWS($3:3),INDEX(RESULTS_RR!$A$3:$A$40,SMALL(INDEX((RIGHT(RESULTS_RR!$AO$3:$AO$40,1)=A$2)*ROW(RESULTS_RR!$AO$3:$AO$40),0),ROWS($3:3)+COUNTIF(RESULTS_RR!$AO$3:$AO$40,"<>*"&A$2))),"")

Column B:

=IF(COUNTIF(RESULTS_RR!$AO$3:$AO$40,"*"&B$2)>=ROWS($3:3),INDEX(RESULTS_RR!$A$3:$A$40,SMALL(INDEX((RIGHT(RESULTS_RR!$AO$3:$AO$40,1)=B$2)*ROW(RESULTS_RR!$AO$3:$AO$40),0),ROWS($3:3)+COUNTIF(RESULTS_RR!$AO$3:$AO$40,"<>*"&B$2))),"")

RESULTS_RR!$AO$3:$AO$40 is where my CURRENT PHASE column is.

B$2 is where my PHASE TITLE is to look up.

RESULTS_RR!$A$3:$A$40 is where my PROJECT NAME column is.

...?

dvent

[Ron Coderre]

After adjusting references, I found the flaw in your formula....

This section: INDEX(RESULTS_RR!$A$3:$A$40
Should be:    INDEX(RESULTS_RR!$A$1:$A$40

So

A3: =IF(COUNTIF(RESULTS_RR!$AO$3:$AO$40,"*"&A$2)>=ROWS($3:3),
INDEX(RESULTS_RR!$A$1:$A$40,SMALL(INDEX((RIGHT(
RESULTS_RR!$AO$3:$AO$40,1)=A$2)*ROW(RESULTS_RR!$AO$3:$AO$40),0),
ROWS($3:3)+COUNTIF(RESULTS_RR!$AO$3:$AO$40,"<>*"&A$2))),"")

Copy that formula across and down.

Done, yet?

[dvent]

Brilliant! Thanks ron!