Macro - matching

Hi,

Apologies. I admit I did not spend time going thru the rules (sorry really caught with work).

Don't mind, can you kindly close this tread for me.

Thank you.

I will.

Thank you.

Hi

See how this goes. It assumes your data is in columns A:C, A1:C1 has headings, and you have an existing sheet called sheet2 to take your output. It will take out matching pairs. So if you have say -27 but 2 matching 27 entries, it will take out the matching pair, and leave one of the 27 entries.

Please Login or Register  to view this content.

rylo

Hi

OK, try this one.

Please Login or Register  to view this content.

If that doesn't get them all, can you attach an example workbook that contains all your possible combinations.


rylo

Tks Rylo.

It works. I'm going to try out on more data (some 60,000 records).

I have a couple of questions on your code hope you can help for my understanding:

1. what does "ce" do?

2. what does "Evaluate" do? there are "--" used within in this function. What're the "--" for?

3. And how about ".address"?

Thanks again.

Hi

1) ce is a variable that takes the reference of each cell in the range as it cycles through

2) Evaluate will evaluate the string that is passed and solve the equation given. It is a way of getting the result of an excel function with out having to try to convert it into a form that VBA can use. The -- is a way of forcing a boolean value to a number. The first - will cause the result to be negative and the second will return it to positive.

3) .address will return the address of the range. So if you have data in the range A2:A20, then irng is set to that range. irng.address will return the string $A$2:$A$20

Basically SUMPRODUCT is a really easy way to get the counts if you use it directly in Excel. However, SUMPRODUCT is difficult to program in VBA, so using EVALUATE is a reasonably easy way to build the formula, then have it return the result.

HTH

rylo

Thanks alot.

I'm guessed I have to spend quite some time deciphering these.

But learnt a great deal too.

Thanks again.

Hi Rylo,

I want to remove the empty rows after the cut & paste.

I tried filtering for blanks and delete them but the funny thing is after the macro filter does not show anything at all; not even those rows that are left.

Any idea what happened?

Hi

Try this.

Please Login or Register  to view this content.

rylo

Originally Posted by rylo

Hi

Try this.

Please Login or Register  to view this content.

rylo

Tks Rylo. This works.

Originally Posted by Chris Bode

Please follow following steps
1.Right click on the toolbar> click Control Box
2.From the control box that appears on your screen, select a command button and draw it to your sheet
3.Double click the command button and write following codes in code window

#

Please Login or Register  to view this content.

#


Hope this works for you

Have a nice time….


Chris
------
Convert your Excel spreadsheet into an online calculator.
http://www.spreadsheetconverter.com

Hi Chris. Tks in advance.

The above works but I need a couple of fine tunings:

1. I need to cut and paste the data to sheet2 instead of copy; and then remove the empty rows after cutting.

2. The data I display here is only partial data, which is the main columns that are required for the matching only. There are actually other columns of data in front and behind. So I need to move the entire rows instead of cell by cell.

Btw, may I know what's "CDbl"?

I would like try both your coding and Rylo's and see which is faster in processing as I have more than 60,000 records every month to process.

Tks.

Originally Posted by dcmb

Tks Rylo. This works.

Rylo,

The macro took a long time to run through some 60,000 rows of data...

Do you have any suggestions for me to cut back on the processing time?

Originally Posted by dcmb

Tks Rylo. This works.

Rylo,

The macro took a long time to run through some 60,000 rows of data...

Do you have any suggestions for me to cut back on the processing time?

Hi

With that number of rows, any processing is probably going to take a while.

Some thoughts.

1) Sort the data by the ID.
thought 1
2) restrict irng and arng to only cover the range of the current ID being processed. This "should" speed up the calculation of the SUMPRODUCT function.

thought 2
3) Add another column that contains a fixed value that is the row number in the source data.
4) Get a unique list of the IDs, then filter the relevant data to a separate working sheet for each of the IDs, and do the matching. Make sure you bring in the row number from the original data.
4) Using the original row number from the worked data, go back to the original and delete that row.

I've got no idea on how long either of those would take to process and if there would be any time savings, but they may be worth a go...

rylo

Tks Rylo.

Thought 1 should be able to save alot of processing time. I'm going to do that.


For thought 2, i will work on it for an enhanced version later. I believed I'm going to need it anyway when my data go pass 65536.

Btw do you have any solutions for excel's data row limit? All the online solutions i searched required installing another add-on.

Many Tks.

Hi

Looking at your version you seem to be using 2007 so the 65k row limit shouldn't apply. Or do you have users with mixed versions?

Another thought is to use MS Access to store your data, and bring in small chunks to process (say by each id at a time). Output can then go to 2 sheets - one that has matches, and the other that is the non matched items. However, you still could have problems if the data is too large, but you should be able to output to different sheets, or columns in the sheets.

rylo

Tks for the suggestions & advise.

I just realised I have another column of data to match.

that means Item must be the same, then followed by Cust ID and then a credit & debit amount.

What should I add in my codes?

HTML Code: 

Hi

Who are you directing the question to? Me or Chris Bode? Which of the code solutions are you using?


rylo

Hi Rylo,

My question is on your codes. I'm using your codes.

Tks in advance.

Hi

Try this. It seemed to work for you example data.

Please Login or Register  to view this content.

rylo