IF formula

[Vishup]

Hi,

Please can anyone help.

I have rows of multiple lines of records of employees within my organisation and out of those some employees are duplicate records. What formula can i use if within the duplicate record rows I need to pick up the employee with a higher sequence number?

For example two rows of data have same employee code but different sequence number say 2 and 1. I need to pick up the employee with sequence 2. My sheet has whole lot of employees like this and the purpose is to pick up the latest sequence in duplicate rows?

I will be very appreciative if someone can help. Thanks.

 

[vasim]

Hi,


Can you post a example file....


You can use the sticky post on the forum named "Posting a sample workbook" for posting the example...

 

[kaushik03]

Hi Vishup,


Welcome to the forum.


Assuming your data spreads across as below:

[pre]

Col (A)        Col (B)
Emp Code	Value
110	          2
110	          1
111	          3
111	          2
113	          4
113	          5
111             8

[/pre]

Now, the formula to get the higher sequence number from duplicate rows would be:


=LARGE(IF($A$2:$A$8=$D$1,$B$2:$B$8,""),1) press CTRL+SHIFT+ENTER (not just enter) from keyboard to enter it as an array formula.


D1 is your employee code stored separately. (First write the employee code at D1 before pasting the formula)


Now, if your employee code at D1 is 111, the formula should return you 8. Change the employee code at D1 to get the respective higher number sequence.


Hope this helps.


Kaushik

 

[Vishup]

Hi Kaushik


Thanks for your response. I applied your formula, it correctly identifies the sequence and the magic works . However I am not able to delete the duplicate rows below the highest sequence as going through the spreadsheet which has so many records and deleting one by one will be so tedious.


How can I send you the actual worksheet? I cant find an upload link?

Thanks

 

[oldchippy]

Hi,


See the Forum's home page and its the 3rd green sticky

 

[Vishup]

http://speedy.sh/Z2XX8/Forward-Dated-Rows-130213-workings.xlsx


I have saved my file above. All I need to do is delete the rows below the highest sequence, now that we have used to formula to identify the highest sequence.


The problem I am facing is that I cannot individually delete the duplicate rows by scanning through the sheet if I was to have ten thousand plus records. So I am looking for a formula which can identify the highest sequence in muptiple rows and help me to delete the rows below the highest sequence as I need to keep only the highest one. Thank you

 

[kaushik03]

Hi Vishup,


Hope you are doing great.


Regarding your next requirement (deleting rows with lower subsequent number(s) for the duplicate IDs), we can certainly achieve that by VBA. If you want to delete rows (at one go) based on some conditions(s), only formula will not help you. But I am not very sure how flexible you are in terms of a VBA based approach. So let us first talk about a formula based approach (obviously not at one go) for this problem.


I believe your end requirement is to get the unique IDs and their higher sequence number.


In the file, that you have uploaded,below I have done:


http://speedy.sh/gKgEE/Forward-Dated-Rows-130213-workings-Autosaved.xlsx


Step1: Give some heading in Col O and R and put filter on. Filter Col R for all the numbers except 0 (zero).


[Logic: To get the IDs which has sequence number >0)


Step2: Copy those list of IDs and sequence numbers in other sheet (here I have copied and pasted that in sheet called “UniqueDataList” in Col A and B). Remember to copy that as visible cells (select the range to be copied, press CTRL+G from keyboard, hit ‘special’, select ‘visible cells only’ hit ‘ok’. Then copy that range) to avoid any unfiltered rows from getting copied.


Step3: Write the formula to get the unique IDs and sequence number.


For unique IDs formula is(at Col E):


=IFERROR(INDEX(DynRngID,MATCH(0,COUNTIF($E$1:$E1,DynRngID),0)),"") CTRL+SHIFT+ENTER to enter it as an array formula, and drag it down as much as you want (as of now I have dragged it till row 200)


Note: DynRngID is a dynamic named range created. To examine this, click CTRL+F3 from keyboard to bring ‘name manager’ dialogue box, select the name range, hit the ‘red dot button’ placed just above the ‘close’ button. You could see the dotted line around the data in Column A. Whenever the data length in Col A would change, dotted line would also adjust to the range.


Then simple Vlookup would help to get the sequence number (see Col F).


Hope this helps.


Let me know if you want a VBA based approach by which you would be able to get the list on click of a button.


Regards,

Kaushik