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

R

Rameses

Board Regular
Joined
Mar 3, 2010
Messages
137
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..?!)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Frequency[/TD]
[TD]Cost[/TD]
[TD]Mode[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Bob[/TD]
[TD]4[/TD]
[TD]7[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Jude[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Mary[/TD]
[TD]3[/TD]
[TD]7[/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
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)

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Frequency
<=4[/TD]
[TD]Frequency
=5[/TD]
[TD]Cost (>=2)[/TD]
[TD]Frequency (<=3) & Cost (>=4)[/TD]
[TD]Frequency (>=4) & Cost (>=4)& Mode (>=4)[/TD]
[TD]Frequency (>=4) & Cost (>=2)& Mode (>=2 BUT <=5)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Bob[/TD]
[TD]Jude[/TD]
[TD]Bob[/TD]
[TD]Mary[/TD]
[TD]Bob[/TD]
[TD]Bob[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Mary[/TD]
[TD][/TD]
[TD]Mary[/TD]
[TD][/TD]
[TD][/TD]
[TD]Jude[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Hi Rameses,

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

For Excel 2007+:

Sheet2[TABLE="class: html-maker-worksheet"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[/TR]
[TR]
[TH]1[/TH]
[TD]Frequency <=4[/TD]
[TD]Frequency=5[/TD]
[TD]Cost (>=2)[/TD]
[TD]Frequency (<=3) & Cost (>=4)[/TD]
[TD]Frequency (>=4) & Cost (>=4)& Mode (>=4)[/TD]
[TD]Frequency (>=4) & Cost (>=2)& Mode (>=2 BUT <=5)[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD]Bob[/TD]
[TD]Jude[/TD]
[TD]Bob[/TD]
[TD]Mary[/TD]
[TD]Bob[/TD]
[TD]Bob[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD]Mary[/TD]
[TD][/TD]
[TD]Jude[/TD]
[TD][/TD]
[TD][/TD]
[TD]Jude[/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD][/TD]
[TD][/TD]
[TD]Mary[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Excel 2010

CellFormula
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>
[TD="bgcolor: #FFFFFF"] Array Formulas [TABLE="class: html-maker-worksheet"]
<tbody>[TR]

</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

For earlier versions:

Sheet2[TABLE="class: html-maker-worksheet"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[/TR]
[TR]
[TH]1[/TH]
[TD]Frequency <=4[/TD]
[TD]Frequency=5[/TD]
[TD]Cost (>=2)[/TD]
[TD]Frequency (<=3) & Cost (>=4)[/TD]
[TD]Frequency (>=4) & Cost (>=4)& Mode (>=4)[/TD]
[TD]Frequency (>=4) & Cost (>=2)& Mode (>=2 BUT <=5)[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD]Bob[/TD]
[TD]Jude[/TD]
[TD]Bob[/TD]
[TD]Mary[/TD]
[TD]Bob[/TD]
[TD]Bob[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD]Mary[/TD]
[TD][/TD]
[TD]Jude[/TD]
[TD][/TD]
[TD][/TD]
[TD]Jude[/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD][/TD]
[TD][/TD]
[TD]Mary[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Excel 2010

CellFormula
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>
[TD="bgcolor: #FFFFFF"] Array Formulas [TABLE="class: html-maker-worksheet"]
<tbody>[TR]

</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
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.
 
Last edited:
Upvote 0
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 :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

gymwrecker
  • Solved
Formula to copy data from one sheet to another sheet based on specific criteria
Replies
4
Views
468
Fluff
Fluff
W
  • Question Question
Move Row to Another Sheet Based on Multiple Cell Values
Replies
9
Views
885
jolivanes
jolivanes
M
  • Solved
Indirect reference of 2 cells in "sheet1" to look up value in table "table1"
Replies
8
Views
155
Kaper
Kaper
H
  • Solved
Copy non-empty cells from the same column from multiple sheets to a sheet "Breeders"
Replies
6
Views
122
mumps
mumps
N
  • Solved
Excel 2016 VBA - Pull data from multiple sheets to One with specificities
Replies
3
Views
668
mumps
mumps

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top