Posts by shiven.k

Hi Forum,Need your help, I am stuck to one problem in Excel. Problem:I have excel file, consists of 4 sheets Data, PF, Gratuity, Discrepancy. Here I have Emp no. field as key field, now here first I have to check Data Sheet data with PF.For example, Suppose, we are considering Emp no.: 4 from data sheet. It will check in PF sheet for same Emp no. if it find then it will check the relationship and first name and last name from data sheet. If it is ok then it will return output in discrepancy sheet with OK. Discrepancy sheet format:[TABLE="class: cms_table, width: 500"]

[tr][td]

Emp No.

[/td][/tr][tr][td][/td][td]

Spouse

[/td][td]

Child1

[/td][td]

Child2

[/td][td]

Father

[/td][/tr][tr][td]

4

[/td][td]

OK

[/td][td]

NA

[/td][td]

NA

[/td][td]

NA

[/td][/tr][tr][td]

9

[/td][td]

OK

[/td][td]

OK

[/td][td]

OK

[/td][td]

NA

[/td][/tr]

[/TABLE](All the relationship present in Data Sheet in Column)Sample Data:Data Sheet[TABLE="class: cms_table, width: 1029"]

[tr][td]

Emp No.

[/td][td]

Name of Employee or Applicant

[/td][td]

Family relationship

[/td][td]

First name

[/td][td]

Last name

[/td][td]

Date of Birth

[/td][/tr][tr][td]

4

[/td][td]

BHATTACHARYA SUBRATA

[/td][td]

Spouse

[/td][td]

SUBARNA

[/td][td]

BHATTACHARYA

[/td][td]

13.11.1973

[/td][/tr][tr][td]

4

[/td][td]

BHATTACHARYA SUBRATA

[/td][td]

Child

[/td][td]

MANJIMA

[/td][td]

BHATTACHARYA

[/td][td]

09.07.2003

[/td][/tr][tr][td]

4

[/td][td]

BHATTACHARYA SUBRATA

[/td][td]

Father / Mother

[/td][td]

HARI NARAYAN

[/td][td]

BHATTACHARYA

[/td][td][/td][/tr][tr][td]

9

[/td][td]

MOHIT SK. ABDUL

[/td][td]

Spouse

[/td][td]

MEHBUBA

[/td][td]

BEGUM

[/td][td]

10.10.1973

[/td][/tr][tr][td]

9

[/td][td]

MOHIT SK. ABDUL

[/td][td]

Child

[/td][td]

SHIRIN

[/td][td]

SULTANA

[/td][td]

28.02.1995

[/td][/tr][tr][td]

9

[/td][td]

MOHIT SK. ABDUL

[/td][td]

Child

[/td][td]

SK.

[/td][td]

SAHIL

[/td][td]

16.04.1998

[/td][/tr]

[/TABLE]PF Sheet [TABLE="class: cms_table, width: 784"]

[tr][td]

Emp. No.

[/td][td]

Sub Type

[/td][td]

First Name

[/td][td]

Last Name

[/td][td]

DOB

[/td][td]

%

[/td][td]

Sex (M/F)

[/td][td]

Relation

[/td][/tr][tr]

[TD="align: right"]4[/TD]

[td]

EPF

[/td][td]

SUBARNA

[/td][td]

BHATTACHARYA

[/td][td]

13.11.1973

[/td]

[TD="align: right"]100[/TD]

[td]

F

[/td][td]

SPOUSE

[/td][/tr][tr]

[TD="align: right"]9[/TD]

[td]

EPF

[/td][td]

MEHBUBA

[/td][td]

BEGUM

[/td][td]

10.10.1973

[/td]

[TD="align: right"]100[/TD]

[td]

F

[/td][td]

SPOUSE

[/td][/tr][tr]

[TD="align: right"]9[/TD]

[td]

EPF

[/td][td]

SHIRIN

[/td][td]

SULTANA

[/td][td]

28.02.1995

[/td]

[TD="align: right"]100[/TD]

[td]

F

[/td][td]

DAUGHTER

[/td][/tr][tr]

[TD="align: right"]9[/TD]

[td]

EPF

[/td][td]

SK MAHMOOD

[/td][td]

ELAHI

[/td][td]

16.04.1956

[/td]

[TD="align: right"]100[/TD]

[td]

M

[/td][td]

SON

[/td][/tr][tr]

[TD="align: right"]11[/TD]

[td]

EPF

[/td][td]

SOMALI

[/td][td]

SARKAR

[/td][td]

09.11.1979

[/td]

[TD="align: right"]100[/TD]

[td]

F

[/td][td]

SPOUSE

[/td][/tr][tr]

[TD="align: right"]14[/TD]

[td]

EPF

[/td][td]

MOUSUMI

[/td][td]

SENGUPTA

[/td][td]

31.08.1967

[/td]

[TD="align: right"]100[/TD]

[td]

F

[/td][td]

SPOUSE

[/td][/tr][tr]

[TD="align: right"]15[/TD]

[td]

EPF

[/td][td]

ALO

[/td][td]

DUTTA

[/td][td]

01.09.1968

[/td]

[TD="align: right"]100[/TD]

[td]

F

[/td][td]

SPOUSE

[/td][/tr][tr]

[TD="align: right"]16[/TD]

[td]

EPF

[/td][td]

SUCHISMITA

[/td][td]

NAG

[/td][td]

21.03.1964

[/td]

[TD="align: right"]100[/TD]

[td]

F

[/td][td]

SPOUSE

[/td][/tr][tr]

[TD="align: right"]17[/TD]

[td]

EPF

[/td][td]

MADHURI

[/td][td]

SINGH

[/td][td]

01.03.1976

[/td]

[TD="align: right"]100[/TD]

[td]

F

[/td][td]

SPOUSE

[/td][/tr][tr]

[TD="align: right"]17[/TD]

[td]

EPF

[/td][td]

ANIKET

[/td][td]

SINGH

[/td][td]

03.09.2001

[/td][td][/td][td]

M

[/td][td]

SON

[/td][/tr][tr]

[TD="align: right"]18[/TD]

[td]

EPF

[/td][td]

EDITH

[/td][td]

D'SA

[/td][td]

29.09.1971

[/td]

[TD="align: right"]100[/TD]

[td]

F

[/td][td]

SPOUSE

[/td][/tr][tr]

[TD="align: right"]23[/TD]

[td]

EPF

[/td][td]

N.

[/td][td]

SHYAMALA

[/td][td][/td]

[TD="align: right"]100[/TD]

[td]

F

[/td][td]

SPOUSE

[/td][/tr]

[/TABLE]Result Sheet[TABLE="class: cms_table, width: 338"]

[tr][td]

E.Code

[/td][td]

PF_Spouse

[/td][td]

PF_Child1

[/td][td]

PF_Child2

[/td][td]

PF_Parent

[/td][/tr][tr]

[TD="align: right"]4[/TD]

[td]

OK

[/td][td]

NA

[/td][td]

NA

[/td][td]

NA

[/td][/tr][tr]

[TD="align: right"]9[/TD]

[td][/td][td][/td][td][/td][td][/td][/tr][tr]

[TD="align: right"]11[/TD]

[td]

OK

[/td][td]

NA

[/td][td]

NA

[/td][td]

NA

[/td][/tr][tr]

[TD="align: right"]14[/TD]

[td]

OK

[/td][td]

NA

[/td][td]

NA

[/td][td]

NA

[/td][/tr][tr]

[TD="align: right"]15[/TD]

[td]

OK

[/td][td]

NA

[/td][td]

NA

[/td][td]

NA

[/td][/tr][tr]

[TD="align: right"]16[/TD]

[td]

OK

[/td][td]

NA

[/td][td]

NA

[/td][td]

NA

[/td][/tr][tr]

[TD="align: right"]17[/TD]

[td][/td][td][/td][td][/td][td][/td][/tr][tr]

[TD="align: right"]18[/TD]

[td]

OK

[/td][td]

NA

[/td][td]

NA

[/td][td]

NA

[/td][/tr]

[/TABLE]And so on.Please help,Thanks,Shivendra

Re: Compare 2 List and extract data in 3rd

Quote from Fotis1991;682019

Nice pictures, but a small sample workbook will be more usefull!

Thanks Fotis for valuable, suggestion

;

Please find the workbook

https://docs.google.com/file/d…EVFRkZWM/edit?usp=sharing

Re: Compare 2 List and extract data in 3rd

I think was not so brief about my problem, further would like to add on to clear my problem

Range of criteria with two match, returning multiple rows

Having 3 Sheets, as shown below. Now, the first sheet is the main sheet and 2nd & 3rd sheet are the first part of condition.

First I have to check 2nd Sheet with 3rd Sheet, for those activity code matches which I did with this formula

Formula in Cell Part1!A4:

=IF(ISERROR(MATCH(Sheet2!A4,Sheet3!A:A,0)),"",Sheet2!A4)

Formula in Cell Part1!B4:
=IF((A4=Sheet2!A4),Sheet2!C4,"")

So, that I pick up the matching data in Part1 as shown below, then there is Part2 where the result needed will be derived by matching Part1 column A & B, as condition and all the matching from Sheet1 will be copied to Part2 Sheet as shown (example):

Sheet1 #

[Blocked Image: http://s23.postimg.org/ma1ct2s93/Sheet1.jpg]

Sheet#2
[Blocked Image: http://s22.postimg.org/uei5cgy8t/Sheet2.jpg]

Sheet#3
[Blocked Image: http://s9.postimg.org/nmkr7w3mz/Sheet3.jpg]

Part#1
[Blocked Image: http://s24.postimg.org/h0amqfy9d/Part1.jpg]

Part#2
[Blocked Image: http://s24.postimg.org/cfa7mwtgx/Part2.jpg]

Re: Compare 2 List and extract data in 3rd

Thanks;

Re: Lookup values in a range using two or more criteria and return multiple matches

Quote from StephenR;681915

You seem to have posted three threads with the same question? Not that you've bothered to articulate one above.

Sorry I might be because of some technical problem.

I have two sheet in raw data, and 2nd sheet is the processed data

The Sample Data are :

Sheet 1:

[TABLE="width: 403"]

[tr]

[td]

AFC001

[/td]

[td]

LBE05ACCFB

[/td]

[td]

G42BE200067

[/td]

[/tr]

[tr]

[td]

AFC001

[/td]

[td]

LBE05ACCFB

[/td]

[td]

G42BE202067

[/td]

[/tr]

[tr]

[td]

AFC001

[/td]

[td]

LBE05ACCFB

[/td]

[td]

G42BE203067

[/td]

[/tr]

[tr]

[td]

AFC001

[/td]

[td]

LBE05ACCFB

[/td]

[td]

G42BE255317

[/td]

[/tr]

[tr]

[td]

AFC001

[/td]

[td]

LBE05ACCFX

[/td]

[td]

G42BE200067

[/td]

[/tr]

[tr]

[td]

AFC001

[/td]

[td]

LBE05ACCFX

[/td]

[td]

G42BE202067

[/td]

[/tr]

[tr]

[td]

AFC001

[/td]

[td]

LBE05ACCFX

[/td]

[td]

G42BE203067

[/td]

[/tr]

[tr]

[td]

AFC001

[/td]

[td]

LBE05ACCFX

[/td]

[td]

G42BE255317

[/td]

[/tr]

[tr]

[td]

AFC002

[/td]

[td]

LBE05CR3DB

[/td]

[td]

IC1BE112700

[/td]

[/tr]

[tr]

[td]

AFC002

[/td]

[td]

LBE05CR3DB

[/td]

[td]

IC1BE112710

[/td]

[/tr]

[tr]

[td]

AFC002

[/td]

[td]

LBE05CR3DX

[/td]

[td]

IC1BE112700

[/td]

[/tr]

[tr]

[td]

AFC002

[/td]

[td]

LBE05CR3DX

[/td]

[td]

IC1BE112710

[/td]

[/tr]

[tr]

[td]

AFC003

[/td]

[td]

LBE05CORPB

[/td]

[td]

G42BE200051

[/td]

[/tr]

[tr]

[td]

AFC003

[/td]

[td]

LBE05CORPB

[/td]

[td]

G42BE202051

[/td]

[/tr]

[tr]

[td]

AFC003

[/td]

[td]

LBE05CORPB

[/td]

[td]

G42BE203051

[/td]

[/tr]

[tr]

[td]

AFC003

[/td]

[td]

LBE05CORPB

[/td]

[td]

G42BE255311

[/td]

[/tr]

[tr]

[td]

AFC003

[/td]

[td]

LBE05CORPF

[/td]

[td]

G42BE200051

[/td]

[/tr]

[tr]

[td]

AFC003

[/td]

[td]

LBE05CORPF

[/td]

[td]

G42BE202051

[/td]

[/tr]

[tr]

[td]

AFC003

[/td]

[td]

LBE05CORPF

[/td]

[td]

G42BE203051

[/td]

[/tr]

[tr]

[td]

AFC003

[/td]

[td]

LBE05CORPF

[/td]

[td]

G42BE255311

[/td]

[/tr]

[tr]

[td]

AFC003

[/td]

[td]

LBE05CORPR

[/td]

[td]

G42BE200051

[/td]

[/tr]

[/TABLE]

Sheet 2:

[TABLE="width: 313"]

[tr]

[td]

AFC001

[/td]

[td]

LBE05ACCFB

[/td]

[/tr]

[tr]

[td]

AFC001

[/td]

[td]

LBE05ACCFX

[/td]

[/tr]

[tr]

[td]

AFC002

[/td]

[td]

LBE05CR3DB

[/td]

[/tr]

[tr]

[td]

AFC002

[/td]

[td]

LBE05CR3DX

[/td]

[/tr]

[tr]

[td]

AFC003

[/td]

[td]

LBE05CORPB

[/td]

[/tr]

[/TABLE]

Result in Sheet 3:

[TABLE="width: 435"]

[tr]

[td]

AFC001

[/td]

[td]

LBE05ACCFB

[/td]

[td]

G42BE200067

[/td]

[/tr]

[tr]

[td]

AFC001

[/td]

[td]

LBE05ACCFB

[/td]

[td]

G42BE202067

[/td]

[/tr]

[tr]

[td]

AFC001

[/td]

[td]

LBE05ACCFB

[/td]

[td]

G42BE203067

[/td]

[/tr]

[tr]

[td]

AFC001

[/td]

[td]

LBE05ACCFB

[/td]

[td]

G42BE255317

[/td]

[/tr]

[tr]

[td]

AFC001

[/td]

[td]

LBE05ACCFX

[/td]

[td]

G42BE200067

[/td]

[/tr]

[tr]

[td]

AFC001

[/td]

[td]

LBE05ACCFX

[/td]

[td]

G42BE202067

[/td]

[/tr]

[tr]

[td]

AFC001

[/td]

[td]

LBE05ACCFX

[/td]

[td]

G42BE203067

[/td]

[/tr]

[tr]

[td]

AFC001

[/td]

[td]

LBE05ACCFX

[/td]

[td]

G42BE255317

[/td]

[/tr]

[/TABLE]

Hi,

I have one problem, want to compare two column and extract the data in 3rd column but the problem with my formula is that blank space are created how to remove it from in between.

Data:

Col - A
xxx01
xxx02
xxx02
xxx05
xxx04
xxx02

Col - B
xxx01
xxx02
xxx03
xxx05

Output
xxx01
xxx02

xxx05

Result Desired
xxx01
xxx02
xxx05

My formula is :
=IF(ISERROR(MATCH(L6,$H$5:$H$24,0)),"",L6)

Re: Unhide Worksheets Automatically

Hi Barb-B,

It was good one, in the same context, I just need one help...condition are as follows:

Sheets:

Data
M6A_Factory
M6A_Region
M5_Factory
M5_Region

Now, if in the Data Sheet Cell A1 has the value = "M5" and Cell A2="Factory" then unhide the sheet named "M5_Factory"

Please help..thanks in advance,

Regards,
Shiven

Re: Automated TimeSheet

Please find the sample workbook;

https://skydrive.live.com/redi…arid=717AEEF8414EDC75!155

Please download from the above link, due to some problem at my side I'm unable to upload it, sorry for inconvinence

Re: Automated TimeSheet

my sample workbook is not getting upload. pls suggest me alternate method to forward the sample workbook

Re: Automated TimeSheet

kindly provide me your email id pls

There are four sheets in my workbook Timesheet, Attend, Leave, Holiday. Here are the abbreviations I want to put in my timesheet relevant for each employee code with the corresponding dates on the top as given on timesheet.

Conditions:
1. If the day exist in holiday sheet then it should be marked as “H” against all employee code in the corresponding column (Done, and its working fine)
2. Then is the employee has taken leave as given in Leave sheet then on the day column relevant it should be marked “L”
3. Again if the employees not taken leave and in Attend sheet -> Absent Column contain “TRUE” then it should be marked as “A”
4. Again if the employee not taken leave and in Attend Sheet ->Exception Column contain “TOUR” then it should be marked as “T”
5. Else rest marked with "P"

P > Present
H > Holiday / Weekly Off
L > Leave
T > On Duty / Tour
A > Absent means unregularised absence

Please help me in making this sheet automate.
Thanks for your kind help.