Forum Discussion
Solved
Excel formula
Hi I have the master record in sheet 1. I want to copy certain rows from sheet 1 to sheet 2 if condition is met. Sheet 1 Column B - department code Column C - Department Description Co...
- Assuming your data in Sheet1 is from Column B:F and the layout of Sheet1 is the same as Sheet2, the formulas you need in Sheet2 are:
D1=IFERROR(LOOKUP(PI(),2/(1/ROW(Sheet1!D:D)=MAX(INDEX(1/ROW(Sheet1!D:D)*(Sheet1!B:B="0000")*(COUNTIF(D$1:D1,Sheet1!D:D)=0),0))),Sheet1!D:D),"")
B1=IFERROR(INDEX(Sheet1!$B:$F,MATCH($D1,Sheet1!$D:$D,,0),COLUMN()-1),"")
After copying D1 down the rows, you then copy B1 to C1, E1:F1, and down such rows also.
Assuming your data in Sheet1 is from Column B:F and the layout of Sheet1 is the same as Sheet2, the formulas you need in Sheet2 are:
D1=IFERROR(LOOKUP(PI(),2/(1/ROW(Sheet1!D:D)=MAX(INDEX(1/ROW(Sheet1!D:D)*(Sheet1!B:B="0000")*(COUNTIF(D$1:D1,Sheet1!D:D)=0),0))),Sheet1!D:D),"")
B1=IFERROR(INDEX(Sheet1!$B:$F,MATCH($D1,Sheet1!$D:$D,,0),COLUMN()-1),"")
After copying D1 down the rows, you then copy B1 to C1, E1:F1, and down such rows also.
D1=IFERROR(LOOKUP(PI(),2/(1/ROW(Sheet1!D:D)=MAX(INDEX(1/ROW(Sheet1!D:D)*(Sheet1!B:B="0000")*(COUNTIF(D$1:D1,Sheet1!D:D)=0),0))),Sheet1!D:D),"")
B1=IFERROR(INDEX(Sheet1!$B:$F,MATCH($D1,Sheet1!$D:$D,,0),COLUMN()-1),"")
After copying D1 down the rows, you then copy B1 to C1, E1:F1, and down such rows also.