• 463K Members
• 6,671 Online
• 560K Conversations
SOLVED

## Excel formula

Occasional Visitor

# 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

Column D - Employee Number

Column E ..... and so on

see image

Now, in Sheet 2, I want to copy all rows if the dept code is 0000.

Can somebody help me? I am a newbie in excel. Thanks

Solution

# Re: Excel formula

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.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies