Forum Discussion

Copper Contributor

Mar 30, 2022

Solved

Looking for Help to indentify duplicates which respect to conditions

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...

Formulas and Functions

Macros and VBA

Riny_van_Eekelen
Mar 31, 2022
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.

types.xlsx19 KB

Riny_van_Eekelen

Platinum Contributor

Mar 31, 2022

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.

types.xlsx19 KB

surya300810

Copper Contributor

Apr 01, 2022

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

Riny_van_Eekelen
Platinum Contributor
Apr 01, 2022
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?
- surya300810
  Copper Contributor
  Apr 02, 2022
  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 ??
  - Riny_van_Eekelen
    Platinum Contributor
    Apr 02, 2022
    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.
- surya300810
  Copper Contributor
  Apr 01, 2022
  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