Match the positive numeric value with negative

[Rajneesh Rawat]

Hi All,

I have one query related to matching the numeric values. I have data set where in column N have numeric values which can be positive and negative. Need to identify same numeric values e.g. -23 should match with 23. My code which is working fine on small set of data like 1000 rows however most of the time we have data more than 50000 rows or 1,000,000 rows. In large data set my code is freezing the whole system may be beacuse i am using two loops in my cod. Does anyone have any quick solution or anykind of excel formula for sorting my issue on large data.

Here is small scenario: my values 34,45,23,-34,43,34,34,-34. As here, 34 is repeating 5 times so any 4 values should be clear and leave the one as it is.

Thanks
Rajneesh

 

[Akuini]

Sorry the above code (post #19 ) is a bit wrong.
You need to change this line:
For i = 1 To f - g
to this:
For i = 1 To f - g + 1

 

[Rajneesh Rawat]

Thanks Akuini for the updated code. It is more efficient as comparison to previous code as its taking very less time apprx 1 minute, while testing this on real data..i got small issue for decimal values such as its matching the amounts 23.03 and 23.08. In that case, I changed the data type from long to double and its working fine for decimal values as well.
I have one more scenario that I believe, we have no solution. In these cases, team also have entries like -23, 4,5,34,10,4,22. Here sum of few values equals to total value as in this series sum of Italic highlighted values is equal to bold highlighted (-23). Nothing is standard in these cases order can be anything, numbers of sum values can be anything.

 

[Akuini]

Thanks Akuini for the updated code. It is more efficient as comparison to previous code as its taking very less time apprx 1 minute, while testing this on real data..i got small issue for decimal values such as its matching the amounts 23.03 and 23.08. In that case, I changed the data type from long to double and its working fine for decimal values as well.
I have one more scenario that I believe, we have no solution. In these cases, team also have entries like -23, 4,5,34,10,4,22. Here sum of few values equals to total value as in this series sum of Italic highlighted values is equal to bold highlighted (-23). Nothing is standard in these cases order can be anything, numbers of sum values can be anything.

I don't understand what you mean.
Can you post some data sample in a table. And maybe you can upload a sample workbook somewhere (like dropbox.com, googledrive etc).

 

[hiker95]

Rajneesh Rawat,

It is always easier to help and test possible solutions if we could work with your actual file.

Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com.

Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

Include a detailed explanation of what you would like to do referring to specific cells and worksheets.

If the workbook contains confidential information, you could replace it with generic data.

 

[Rajneesh Rawat]

Hi Akuini & Hiker,

Due to some official restriction, these sites are not accessible from my laptop. I can try to explain you the scenario, in my previous scenario, we find the same numeric values like 23 & -23, this values highlighted in yellow. Now my query is if suppose there is no value equals to any numeric value then we will try to sum of any two or more numbers which is not highlighted in yellow and try to offset these amount like for 23, we can check this by sum these values if exist in my data -7,-10,-6 or -3,-4,-4,-7,-5 clear from these numeric value as sum of these values is -23.

 

[Akuini]

Hi Akuini & Hiker,

Due to some official restriction, these sites are not accessible from my laptop. I can try to explain you the scenario, in my previous scenario, we find the same numeric values like 23 & -23, this values highlighted in yellow. Now my query is if suppose there is no value equals to any numeric value then we will try to sum of any two or more numbers which is not highlighted in yellow and try to offset these amount like for 23, we can check this by sum these values if exist in my data -7,-10,-6 or -3,-4,-4,-7,-5 clear from these numeric value as sum of these values is -23.

Sorry, still don't understand what you want. Maybe you can post some example in a table to describe it better.