Filtering, sorting and finding missing sequence using macro (for big Excel file)

[TallOne]

Hi All

I have a very big Excel file (62 MB). I need to be able to filter by one column (FACILITY) and sort the whole file using the (REGISTRATION NO) column as well as finding the missing sequence number in the (REGISTRATION NO) column. The problem is that the (FACILITY) column has more than 200 different facility name. I am thinking of macro as I have different Spreadsheet I need to do the same steps in each one separately.

I have attached a copy (example from the data that I have)


Any help please.

[TallOne]

Any help please.

[jaslake]

Hi TallOne

Are these three separate tasks or are they somehow integrated?

[TallOne]

Hi Jaslake,
Thank you for your reply.
They are integrated. I Need first to filter my data using (FACILITY) column, sort all the using the (Regards No) and then finds the missing sequence in the (Region No) column.

Thanks again.

[jaslake]

Hi TallOne

So, I Filter on ADRIAANSE CLINIC and get this...now what do you wish to happen...spell it out, step by step...at this point I'm not following.

	A	B	C	D	E	F	G	H	I
1		LAST UPDATED	FACILITY	REGISTRATION NO.	GENDER	AGE	REGISTRATION DATE	TREATMENT (RX) START DATE	REGIMEN
2	418	01/08/2012	ADRIAANSE CLINIC	64	Male	38	31/03/2010	24/03/2010	2: 2HRZES 1HRZE 5HRE - Reg 2
3	598	01/08/2012	ADRIAANSE CLINIC	31	Female	6	10/02/2010	10/02/2010	3: 2HRZ 4HR - Reg 3
4	837	01/08/2012	ADRIAANSE CLINIC	27	Male	37	08/02/2010	08/02/2010	1: 2HRZE 4HR - Reg 1
5	995	01/08/2012	ADRIAANSE CLINIC	229	Male	29	25/10/2010	20/10/2010	1: 2HRZE 4HR - Reg 1

[TallOne]

Hi Jaslake,
After that I need to sort the using the (Regards No) column, then finding all the missing number in the (Region No).
The data that I have is very big (more than 50000 rows and more than 200 columns). Also the (Facility) column has 200 different values (clinic name).

Thanks again, I really appreciate your help.

[jaslake]

Hi TallOne

OK, I've sorted ADRIAANSE CLINIC and get the attached...what Numbers are missing...1 to 26, 28 to 30, 32 to 63 and 65 to 228?

If so, what kind of report do you want? Do you want that report for each of your 200 facilities? What if the Facility has only 1 Registration Number? Help me with the output you're looking for.

	A	B	C	D	E	F	G	H	I
1		LAST UPDATED	FACILITY	REGISTRATION NO.	GENDER	AGE	REGISTRATION DATE	TREATMENT (RX) START DATE	REGIMEN
2	837	01/08/2012	ADRIAANSE CLINIC	27	Male	37	08/02/2010	08/02/2010	1: 2HRZE 4HR - Reg 1
3	598	01/08/2012	ADRIAANSE CLINIC	31	Female	6	10/02/2010	10/02/2010	3: 2HRZ 4HR - Reg 3
4	418	01/08/2012	ADRIAANSE CLINIC	64	Male	38	31/03/2010	24/03/2010	2: 2HRZES 1HRZE 5HRE - Reg 2
5	995	01/08/2012	ADRIAANSE CLINIC	229	Male	29	25/10/2010	20/10/2010	1: 2HRZE 4HR - Reg 1

[TallOne]

Thanks for your quick reply and help.

I want that for each facility, i.e, the same above steps. I need to use macro (if possible) because I have different spreadsheets with the same columns heading.

Thanks again.

[jaslake]

Hi TallOne

Dummy up an output File that demonstrates your desired output. Do so for 3 Facilities including ADRIAANSE CLINIC. I'm not getting the picture...paint it for me. What do you want the Output to be?


The Filtering is not a problem; Sorting is not a problem; the Missing Numbers is not a problem depending on your answer to my question

what Numbers are missing...1 to 26, 28 to 30, 32 to 63 and 65 to 228?

Please respond to this question.

[TallOne]

Hi
Yes, these the number are missing in the facility.
I do have output file for 10 facilities, that I did manually. Will try to delete some of the columns so that I can uploaded.

Thanks again.

[TallOne]

Hi again,
The desired output:
1- for each facility, identify the missing number in the (Region No) column.
2- the output should be in new sheet; or new workbook.

Hope that helps.

[jaslake]

Hi TallOne

I'll wait until I see this...I'll just be guessing otherwise...

I do have output file for 10 facilities, that I did manually.

[TallOne]

Hi jaslake,

Please see the attached file.

I have only filtered by facility, and sorted the data using (Reg No) column, but I need to find the missing number in the (Reg No) column. After that I have to do some calculations and graphs, and I think I will be able to do so.

Thanks again for your time and help

[jaslake]

Hi TallOne

Modify this File for the "missing numbers".

[TallOne]

Hi Jaslake,

Here is the modified file with the "missing number". Is it possible to find the missed number instead of the way I did in the attached file.


Thanks again for your time and help.

[TallOne]

Hi All

And is it possible to have the output in a new workbook.

Thanks in advance

[jaslake]

Hi TallOne

If you were to show me what you want the output to be perhaps it can be accommodated.

Is it possible to find the missed number instead of the way I did in the attached file.

I believe I've asked for this several times already and I'll not spend more time on your issue until you've defined what it is you want/need...sorry.

[TallOne]

Hi Jaslake

Please see the attached file (This the output that I can think of till now, my data is very big so I still have a lot work to do with it).

Thank you very much for your time and help.

[jaslake]

Hi TallOne

Do you want a new Workbook with ALL Facilities in the SAME Workbook or do you want a SEPARATE Workbook for EACH Facility?

[TallOne]

Hi Jaslake,
Thank you very much for your effort.
All the facilities in the same workbook.

Thanks again.

[jaslake]

Hi TallOne

This Code is in the attached. It takes a bit of time to run and, of course, will take longer on your Actual File.

Run the Code first on the attached Sample File to see if the results are as you described. CTRL + x will fire the Code. The Output File (named "Facilities") will be in the same Folder as the Sample File.

Please Login or Register  to view this content.

[TallOne]

Hi Jaslake,

Thank you very much for your effort and help. I have checked just now, will play around it and let you know.

Thanks again.

[TallOne]

Hi Jaslake,

It works fine, thanks a lot for your help.

Is it possible to create a new workbook showing the missing sequence for each facility (Please see the output I am looking for)


Thank you again.

[jaslake]

Hi TallOne

Those numbers in your Sample File mean nothing to me...where do they come from...what do they represent? Tie them to the actual output from the File we're working with so I can understand what they mean.

[TallOne]

Hi Jaslake,

Thank you for reply.

What I meant is that: the missing sequence that you calculated; I want them to be in a new workbook (missing sequence per facility).

The number in the previous attached was only to show you the output that I'm looking for.

Thanks again.

[TallOne]

Hi Jaslake,

Please see the attached file (the output I am looking for using the data we were working with)


Thanks again

[TallOne]

Any help please.

[jaslake]

Hi TallOne

I'm going to be tied up a bit...get back to it as soon as I can...

[jaslake]

Hi TallOne

Try the Code in the attached...

[TallOne]

Hi Jaslake,

That was perfect, exactly what I am looking for.

Thank you for your help and time really appreciated.

[TallOne]

Hi Again,

I was trying to sort out this problem and I came up with this code, but yours is perfect. I just want to share it with others

Please Login or Register  to view this content.

[jaslake]

You're welcome...glad I could help. Thanks for the Rep.