If on Sheet 1 a cell meets a certain criteria then copy the contents of another cell (from sheet 1) to a certain cell on sheet 2. In sheet 2 make sure

[Rameses]

Hi

I am trying to find a formula to do the following

Ideally a worksheet formula rather than an array if possible

If on Sheet 1 a cell meets a certain criteria then copy the contents of another cell (from sheet 1) to a certain cell on sheet 2. In sheet 2 make sure they are listed in a specified column based on that criteria.

Sheet 1 is below and Sheet 2 is on the next post (as I couldnt get it under..?!)

	A	B	C	D
1	Name	Frequency	Cost	Mode
2	Bob	4	7	4
3	Jude	5	2	2
4	Mary	3	7	7

<tbody>
</tbody>

 

[Rameses]

I wish to sort the data from sheet 1 into sheet 2 below
I have put the Outcomes in bold (it is thesse that I need a formula for)

<tbody>
</tbody>

 

[Rameses]

I wish to sort the data from sheet 1 into sheet 2 below
I have put the Outcomes in bold (it is these that I need formulas for) (nb am working out row 2 & 3 (criteria is in the colomns)

I hope this makes sense I have tried to show it in the simplest of forms

Any help at all gratefully received

Rameses

	A	B	C	D	E	F	G	H
1	Frequency <=4	Frequency =5	Cost (>=2)	Frequency (<=3) & Cost (>=4)	Frequency (>=4) & Cost (>=4)& Mode (>=4)	Frequency (>=4) & Cost (>=2)& Mode (>=2 BUT <=5)
2	Bob	Jude	Bob	Mary	Bob	Bob
3	Mary		Mary			Jude

<tbody>
</tbody>

 

[Rameses]

Ithink I have stumped excel MVP's!!! surely not...?

 

[Rameses]

bump

 

[Rameses]

Can anyone help me at all... I am really desperate please..........

 

[Rameses]

Hi Guys

any help would be really appreciated

 

[circledchicken]

Hi Rameses,

Depending on the version of Excel are you using, try the following:

For Excel 2007+:

Sheet2

	A	B	C	D	E	F
1	Frequency <=4	Frequency=5	Cost (>=2)	Frequency (<=3) & Cost (>=4)	Frequency (>=4) & Cost (>=4)& Mode (>=4)	Frequency (>=4) & Cost (>=2)& Mode (>=2 BUT <=5)
2	Bob	Jude	Bob	Mary	Bob	Bob
3	Mary		Jude			Jude
4			Mary
5

<tbody>
</tbody>

Excel 2010

Array Formulas Cell Formula A2 =IFERROR(INDEX(Sheet1!$A$2:$A$4, SMALL(IF(Sheet1!$B$2:$B$4<=4, ROW(Sheet1!$A$2:$A$4)-ROW(Sheet1!$A$2)+1), ROWS($A$1:$A1))), "") B2 =IFERROR(INDEX(Sheet1!$A$2:$A$4, SMALL(IF(Sheet1!$B$2:$B$4=5, ROW(Sheet1!$A$2:$A$4)-ROW(Sheet1!$A$2)+1), ROWS($A$1:$A1))), "") C2 =IFERROR(INDEX(Sheet1!$A$2:$A$4, SMALL(IF(Sheet1!$C$2:$C$4>=2, ROW(Sheet1!$A$2:$A$4)-ROW(Sheet1!$A$2)+1), ROWS($A$1:$A1))), "") D2 =IFERROR(INDEX(Sheet1!$A$2:$A$4, SMALL(IF(Sheet1!$B$2:$B$4<=3, IF(Sheet1!$C$2:$C$4>=4, ROW(Sheet1!$A$2:$A$4)-ROW(Sheet1!$A$2)+1)), ROWS($A$1:$A1))), "") E2 =IFERROR(INDEX(Sheet1!$A$2:$A$4, SMALL(IF(Sheet1!$B$2:$B$4>=4, IF(Sheet1!$C$2:$C$4>=4, IF(Sheet1!$D$2:$D$4>=4, ROW(Sheet1!$A$2:$A$4)-ROW(Sheet1!$A$2)+1))), ROWS($A$1:$A1))), "") F2 =IFERROR(INDEX(Sheet1!$A$2:$A$4, SMALL(IF(Sheet1!$B$2:$B$4>=4, IF(Sheet1!$C$2:$C$4>=2, IF(Sheet1!$D$2:$D$4>=2, IF(Sheet1!$D$2:$D$4<=5, ROW(Sheet1!$A$2:$A$4)-ROW(Sheet1!$A$2)+1)))), ROWS($A$1:$A1))), "") <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself

<tbody>
</tbody>

For earlier versions:

Sheet2

	A	B	C	D	E	F
1	Frequency <=4	Frequency=5	Cost (>=2)	Frequency (<=3) & Cost (>=4)	Frequency (>=4) & Cost (>=4)& Mode (>=4)	Frequency (>=4) & Cost (>=2)& Mode (>=2 BUT <=5)
2	Bob	Jude	Bob	Mary	Bob	Bob
3	Mary		Jude			Jude
4			Mary
5

<tbody>
</tbody>

Excel 2010

Array Formulas Cell Formula A2 =LOOKUP("zzz", CHOOSE({1,2}, "", INDEX(Sheet1!$A$2:$A$4, SMALL(IF(Sheet1!$B$2:$B$4<=4, ROW(Sheet1!$A$2:$A$4)-ROW(Sheet1!$A$2)+1), ROWS($A$1:$A1))))) B2 =LOOKUP("zzz", CHOOSE({1,2}, "",INDEX(Sheet1!$A$2:$A$4, SMALL(IF(Sheet1!$B$2:$B$4=5, ROW(Sheet1!$A$2:$A$4)-ROW(Sheet1!$A$2)+1), ROWS($A$1:$A1))))) C2 =LOOKUP("zzz", CHOOSE({1,2}, "",INDEX(Sheet1!$A$2:$A$4, SMALL(IF(Sheet1!$C$2:$C$4>=2, ROW(Sheet1!$A$2:$A$4)-ROW(Sheet1!$A$2)+1), ROWS($A$1:$A1))), )) D2 =LOOKUP("zzz", CHOOSE({1,2}, "",INDEX(Sheet1!$A$2:$A$4, SMALL(IF(Sheet1!$B$2:$B$4<=3, IF(Sheet1!$C$2:$C$4>=4, ROW(Sheet1!$A$2:$A$4)-ROW(Sheet1!$A$2)+1)), ROWS($A$1:$A1))))) E2 =LOOKUP("zzz", CHOOSE({1,2}, "",INDEX(Sheet1!$A$2:$A$4, SMALL(IF(Sheet1!$B$2:$B$4>=4, IF(Sheet1!$C$2:$C$4>=4, IF(Sheet1!$D$2:$D$4>=4, ROW(Sheet1!$A$2:$A$4)-ROW(Sheet1!$A$2)+1))), ROWS($A$1:$A1))))) F2 =LOOKUP("zzz", CHOOSE({1,2}, "",INDEX(Sheet1!$A$2:$A$4, SMALL(IF(Sheet1!$B$2:$B$4>=4, IF(Sheet1!$C$2:$C$4>=2, IF(Sheet1!$D$2:$D$4>=2, IF(Sheet1!$D$2:$D$4<=5, ROW(Sheet1!$A$2:$A$4)-ROW(Sheet1!$A$2)+1)))), ROWS($A$1:$A1))))) <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself

<tbody>
</tbody>

Note:

• Change the data ranges as required for your actual dataset
• I think there might be a mistake in your sample output in post #3, column C should have Jude as well as the criteria is >=2.
• These are worksheet array formulae - you have to press Ctrl-Shift-Enter rather than just Enter, then you can drag them down as normal. Unfortunately I don't know how to construct non-array equivalents.
• You should start to see a pattern in terms of how the criteria are are factored in with the IF statements - the rest of the formula doesn't change across the columns.
• Finally, I don't think its a good idea to bump your thread so often - it's unlikely to get many replies if you do.

 

[Rameses]

Circled Chicken

I just wanted to say thankyou so much your answer was straight to follow and awesome!

This really helped me massively

I am extremely grateful that you took the time to work out each example.

Rameses

 

[circledchicken]

Your welcome! Thanks for the feedback.