Looking for Help to indentify duplicates which respect to conditions

[surya300810]

Looking for a formula for getting the duplicate data based on the highest version and then copy adjacent row data in next rows . 

Example : 

InpUt 

Name	Set Type	Version
95591	Type 1	-
95692	Type 3	-
95692	Type 1	-
96292	Type 3	-
96292	Type 3	A
96292	Type 3	B
96292	Type 1	B
96292	Type 2	B
96293	Type 3	-
96293	Type 3	A
96293	Type 3	B
96293	Type 2	B
96293	Type 1	B
87665	Type 1	B
87665	Type 3	B
87665	Type 1	C
87665	Type 3	C
87665	Type 1	D
87665	Type 3	D
87665	Type 1	E
95597	Type 1	-
95597	Type 2	-
95598	Type3	-
95598	Type2	-

Output 

Name	Set Type	Version
95591	Type 1	-	Type 1
95692	Type 3	-
95692	Type 1	-
96292	Type 3	-
96292	Type 3	A
96292	Type 3	B	Type 1	Type 2	Type 3
96292	Type 1	B
96292	Type 2	B
96293	Type 3	-
96293	Type 3	A
96293	Type 3	B	Type 1	Type 2	Type 3
96293	Type 2	B
96293	Type 1	B
87665	Type 1	B
87665	Type 3	B
87665	Type 1	C
87665	Type 3	C
87665	Type 1	D
87665	Type 3	D			Type 3
87665	Type 1	E	Type 1
95597	Type 1	-	Type 1	Type 2
95597	Type 2	-
95598	Type3	-		Type2	Type3
95598	Type2	-

[#2]

@surya300810 

I'm trying to understand what you want to do. Does the attached file show your intended result?

For example in the post there is the input

Name	Set Type	Version
95591	Type 1	-

with the intended output:

Name	Set Type	Version
95591	Type 1	-	Type 1

However there isn't a duplicate for Name 95591 and Version - in the data.

[#3]

@OliverScheurich 

Actually , I need to eliminate the duplicates for the all the lower revision for the  set types

if there is no duplicate entry then we need to keep it and if we have different versions for different set types even for the same number then also we need to show it in the out put

Ideal Output

[#4]

Can you share the code for the Above attached PDF - Which will Solve half of my problem.

[#5]

@surya300810 

The data in the pdf. file was entered manually. Unfortunately i can't help you with what you actually want to do. I can't suggest a solution to select the duplicate names of the highest version with a formula or VBA.

EDIT: You can click the button in cell H2 in the attached file to start the macro. This is the only solution i can offer. The result is what i entered manually in the pdf. file.

[#6 · Accepted solution]

@surya300810 Perhaps Power Query can do what you need. See attached file. Note that I changed some of the data as I believe it didn't coincide with the description/explanation in one of the screenshots you uploaded. I marked the changes yellow in the Input list.

[#7]

Thanks for the response , and you solution is very near to the ideal solution only thing is my data set have one issue, that it is possible to have different versions for different type set - which is one of the mistakes in the data set which I need to identify and correct it - So the version value in row no 22 is correct as per the input data set

[#8]

@surya300810 Not sure I follow. If C22 should indeed be a D, then it will no longer be included in the PQ generated table. Change it yourself and press Refresh All on the Data ribbon and see that item 87665 will only show E and Type 1. Isn't that what you want?

[#9]

yes correct , what i though was - I expected a line item with D as well along with E - But , Now i think it will create further problem in my data set if i take it the way i mentioned initally.

The Power query what you gave should do the work .Thanks you for the help

[#10]

@surya300810Can't we use pivot table???? 

[#11]

power query you provided Is taking only 25 rows of data , when I try to change source I have error , how can I define the range of row to 125000 in the power query input ??

[#12]

@surya300810 In my example I named the range A3:C27 as "Input" and the query is connecting to that named range. You need to first define a name for your range (or create a table from it) and then change the Source step in the query so that it connects to that range or table.