Copying rows from source sheet to new sheet based on color derived in a column by conditional formatting in the source sheet

aks9389

New Member
Joined
Apr 4, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Hello everyone,

I have been studying and following many sources to get the desired result but in vain. I have followed this link as well as this another link .I will give a brief description of the desired result in the attached excel sheet.

The first 3 sheets "FIRST50", "FIRST100" & "FIRST200" are the data source files and have been condtionally formatted using 4 rules given therein. i have copied here under as well:

1)=AND($B2=$C2,($B2-$D2)>10) Darkred(RBG: 192,0,0) fill in the respective row
2)=AND($C2-$B2<2,$B2<>$C2,$B2<>$D2,($B2-$D2)>20) resulting in Red(RBG: 255,0,0) fill in the respective row
3)=($B2=$D2) resulting in Green(RBG: 0,176,80) fill in the respective row
4)=AND($B2<$D2+5,$B2<>$C2,$B2<>$D2,($C2-$B2)>5) resulting in Lightgreen(RBG: 146,208,80) fill in the respective row

I have the following requirement based on color fill coming out of conditional formatting:

From sheet "FIRST50", all rows satifying conditions 1&2 to be copied to sheet "SHORT50" and all rows satisfying conditions 3&4 to be copied to sheet "LONG50"
From sheet "FIRST100", all rows satifying conditions 1&2 to be copied to sheet "SHORT100" and all rows satisfying conditions 3&4 to be copied to sheet "LONG100"
From sheet "FIRST200", all rows satifying conditions 1&2 to be copied to sheet "SHORT200" and all rows satisfying conditions 3&4 to be copied to sheet "LONG200"

As per my understanding (may be wrong !), first step will be to identify the colors as per CF and second step would be to copy and paste in intended sheets. However, I am stuck in first step itself as code given in the excel sheet gives the error as #VALUE and ?NAME. Please suggest if I need to make any changes or change the code itself.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

HighAndWilder

Board Regular
Joined
Nov 4, 2006
Messages
173
Office Version
  1. 365
Platform
  1. Windows
I would ignore the fact that conditional formatting has been set up and use the criteria that has been used for the conditional formatting
to detemine which rows to copy.

You could set up a temporary helper column with a formula that evaluates to the name of the worksheet to copy the rows to.
The VBA could set this up and then delete it when the copying is done.

Filter the rows and then copy the filtered rows to the appropriate sheet.

Let me know if you need any help to set this up.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,791
Office Version
  1. 365
Platform
  1. Windows
If you create a helper column in col M on the First50 sheet & put this in M2 & fill down
=OR(AND(B2=C2,(B2-D2)>10),AND(C2-B2<2,B2<>C2,B2-D2>20))

You can then use
=IFERROR(INDEX(FIRST50!A$2:A$100,AGGREGATE(15,6,(ROW(FIRST50!A$2:A$100)-ROW(FIRST50!A$2)+1)/(FIRST50!$M$2:$M$100),ROWS(A$2:A2))),"")
In A2 of Sort50 copied down & across.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,791
Office Version
  1. 365
Platform
  1. Windows
For the long sheets use in Col N of First50
=OR(B2=D2,AND(B2<D2+5,B2<>C2,B2<>D2,C2-B2>5))

and then on long50
=IFERROR(INDEX(FIRST50!A$2:A$100,AGGREGATE(15,6,(ROW(FIRST50!A$2:A$100)-ROW(FIRST50!A$2)+1)/(FIRST50!$N$2:$N$100),ROWS(A$2:A2))),"")
 

aks9389

New Member
Joined
Apr 4, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
For the long sheets use in Col N of First50
=OR(B2=D2,AND(B2<D2+5,B2<>C2,B2<>D2,C2-B2>5))

and then on long50
=IFERROR(INDEX(FIRST50!A$2:A$100,AGGREGATE(15,6,(ROW(FIRST50!A$2:A$100)-ROW(FIRST50!A$2)+1)/(FIRST50!$N$2:$N$100),ROWS(A$2:A2))),"")

Thanks Fluff, for making things simple, that very well serves my purpose. I have modified range of A to 1000 instead of 100 to maintain uniformity of formula across all 3 source sheets. Thank You very much.

One problem i am facing with the updated sheet (attached here): I am not able to sort the data in any of the 3 "short" or "long sheets" as it returns back to the original set, perhaps due to existing filter conditions we have used. For eg, when I am trying to sort the full set of filtered data in SHORT50 by ascending order of the data in column G ("CHGN"), it first sorts but goes back to original condition. One way i could try is to copy the filtered data and paste as 'values' in different sheet and then sort but that takes time. I am wondering if I can tweak something in the formula itself so that it allows me to sort the data by any of the columns.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,791
Office Version
  1. 365
Platform
  1. Windows
Unfortunately you cannot sort the data on those sheets. However if you sort the data on the master sheet, that will be reflected on the short & long sheets.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,059
Messages
5,639,816
Members
417,116
Latest member
Jakeyw

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
Top