remove shuffled duplicates

[vs140580]

Suppose a1,b1,c1,d1,e1 are elements of a row and a1,c1,e1,d1,b1 is some permutation of the elements of that row how to retain only one row among all such permutations in several rows and delete remaining I dont know VBA prograoming kind help.

 

[sheetspread]

If the permutations arise from SQL joins then avoid them by changing the original query (if you can). Otherwise maybe a UNION query or reverse pivot table followed by an ORDER BY(sort) that you can re-pivot.

 

[Peter_SSs]

THe question now is if they are spread across workbooks due to large data files

How difficult would it be to consolidate them into one with perhaps a reference number for each line?

My interpretation of the OPs remark was that the data is spread across multiple files because it will not fit in one - that is >1,048,576 rows &/or greater than Excel's column limit.

If the data files are very big it would also mean that a sorting formula solution would probably grind the sheet to a halt.

 

[sheetspread]

My interpretation of the OPs remark was that the data is spread across multiple files because it will not fit in one - that is >1,048,576 rows &/or greater than Excel's column limit.

If the data files are very big it would also mean that a sorting formula solution would probably grind the sheet to a halt.

Yes, I've seen how slow even simple formulas can be on a million rows with just several columns. Like you say, it's now either the scripting dictionary or SQL/BI