Filtering / Sorting And Finding Missing Sequence Using Macro (for Big Excel File)

May 27, 2014

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

View 14 Replies


Excel 2007 :: Macro For Finding Out Missing Number And Duplicate Number In A Range

Jan 22, 2013

Excel 2007

View 7 Replies View Related

Excel 2007 :: Password Protect Sheet But Allow Filtering And Sorting

Jan 30, 2014

I am using Excel 2007 and I have a worksheet with multiple columns, some of these columns are protected and some are not.

I then lock the worksheet with a password so the user can't edit certain cells that are protected but can modify the unprotected cells, the problem is the user cannot use the filter or sort the workbook.

Is there a way to allow filtering or sorting but still lock down the worksheet.

View 3 Replies View Related

RANK Duplicate Numbers Without Missing In Sequence?

Jul 18, 2014

Attempting to do a spreadsheet for my golf club, to record scores, etc...

Managed to get it to pretty much what I want despite some very extreme looking formulae! So for some scores such as 56,56,57,57,54,54, Rank would just give me 3,3,5,5,1,1, so I have put a second column next to 56,56,57,57,54,54 where I input scores to seperate ties (better back nine scores), so in the above I would have for example 30,29 next to the two 56s, 27,29 next to the two 57s, and 27,28 next to the two 54s, so the rank would return 4,3,5,6,1,2. I have gone three stages further, so if, the back nine score was also tied, I have a column for the last 6 hole score, the last 3 hole score and even the last hole score. This is some serious formulae at this point. However, I now want the OPTION to rank the original data as 2,2,3,3,1,1 if I don't put any data in the back nine column.

This is so that I can input doubles scores on the same spreadsheet as singles scores, so if two people shoot the same score in singles I can input the back 9 scores etc to decide who comes out ahead, but in doubles I would just leave the back nine score blank, and then two people would share 1st in the rank, but then I need two 2nd ranks NOT 3rd ranks as it currently is.

Just so you can see how complex it is the current forumlae in the rank is here is an example:


[Code] .....

There are other things in there to sort out a few other issues as well, but I cant see the wood for the trees so to speak, to get it todo what I want!

I have also attached a sample from my spreadsheet to better demonstrate what I want!

View 12 Replies View Related

Sorting And Filtering ..

Nov 29, 2007

in the table i have


I need one list that will show me all items that are on list so i would get:


to simpilfy this list would show me what types are there in the first list

View 9 Replies View Related

Wildcard Filtering/sorting

Feb 5, 2010

We have an ISP based antispam system which can sometimes incorrectly pickup messages as Spam. I would like to scan through all quarantined messages every week to check that this does not happen. I can export a report from the program to excel but what I would like to do is remove subject lines with common spam words (Viagra, Russian etc) so I don’t need to check these, this would in effect reduce the report by 85%.

Ideally the ‘spam word’ list would be on the second sheet as in example and could be added to as I find obvious words.

The ideal end result would either be all lines which have a word from the ‘Spamwords’ sheet are removed or all lines which do not match the words in the spam list are copied to a new sheet.

View 8 Replies View Related

Sorting/filtering Lists

May 30, 2009

I have a list with rows containing NAME, CLUB and TIME (A5:C124).

I'd like to be able to create a new list which would contain the fastest 3 TEAMS along with the combined time (SUM) of the fastest 3 times for each CLUB. Not all CLUBS would have 3 entries and these would need to be excluded.

View 10 Replies View Related

Sorting And Filtering The Records To New Worksheet

Jan 14, 2009

I need to do sorting and filtering the similar records to new worksheet. I need you help to do this job, give some idea or command to use for the following steps.

I have attached the worksheet newtran.xls.

1. I need to find the last column, the column will be increased or decreases. so that we need to find the last column at time of running the macro. Now the value is there till column DD

2. I need to concatenate the column B, D, F to last column find out at 1 step. Insert a new column and place the concatenated value.

3. Entire row will be Sorted based on concatenated value column.

4. Find the similar row on concatenated column and entire row need to be moved to new worksheet.

View 11 Replies View Related

Sorting / Filtering Specific Letters In Random Code

Dec 7, 2013

I have an excel database that contains a code to identify specific people.

Jones 3 Quay St, PN 063586954 JU79N4
White 24 Dyk St, PN 063547786 9GVJ64
Smith 9 Random St, PN 063512698 4LN867
Butt 89 Yeah Pl, PN 063569986 D920HK
Handle 69 James Ct, PN 06 3549687 ZK26S84

If I wanted to filter the list so I only had codes that had Z, N, H in it. How do I do that??

View 5 Replies View Related

Sorting (or Maybe Filtering) Worksheet With Multiple Data In Cells

Nov 1, 2008

Background: I am HR manager for a construction company & keeper of the call-in list of personnel who are looking for work. I have a simple sheet that has columns:

Date Name Craft Experience ...more info...

If each call-in had only one craft, wouldn't have a problem. Those who are multicrafted ar listed e.g. "EL, MW, BM" In the column C. A caller two days later may be listed as "MW, BM, EL" We input the data as they say it since that is usually their order of expertise. (Yes, I know that it should have been set up with each craft having its own column, but I inherited the sheet & it has 4000+ entries)

I wrote a couple of small macros & assigned buttons on the sheet to allow the users to sort the sheet by date, or name, or craft. My customers (project managers) have requested to be able to sort by craft but have all the folks with any specific craft listed together.

Example (Excel 2003): ..

View 4 Replies View Related

Inserted Objects In Cells Move When Filtering Or Sorting

Apr 23, 2013

Here is the original table:

This is with a filter on:

You can see there that some inserted object (in this case, PDF files shown as icons), are moved. I need to find a way to immobilize every inserted object within each cell boundaries. I want to freely sort or filter and avoid this kind of problem that gets worse with more rows, columns and inserted objects.

View 1 Replies View Related

Finding Missing Data Using Arrays And 2 Tables

Aug 25, 2014

Any easy way to retrieve data from another table using an array formula.

I have two files that I am using that contain 2 sets of data with columns for name, address, city, and state. The red highlighted data needs be used to find the blue data first column, which is a possible name for the company found from the red data. The issue is that the blue data is larger and has rows of data that will not be found in the other table.

I have the spreadsheet attached. I attempted to use an if/and statement :
but found out that it does not work with arrays and only found the first address by default.

How would I set up a formula to retrieve the possible names using criteria from the blue data such as if the address and city match, then input that company name?

View 5 Replies View Related

Macros / Finding Missing And Complete Addresses?

Mar 10, 2014

I'm trying to analyze some data from customers where it finds how many phones were missing, zip codes, city, states etc.. However, im having trouble finding a solution to find complete addresses and tally it. I have 2 address columns and one either has a PO box or a normal address. With my code, if one address cell is empty, it will consider it as an incomplete address but we all know it is still a whole and complete address.

View 2 Replies View Related

Finding A Missing Date And Listing Name From Next Column

Jan 27, 2010

I am try to show a list of all rows that have a missing date in column "B" and then show the corrasponding name in the next column "C". I can find the first one on the sheet and how many have missing dates using:

View 6 Replies View Related

Comparing 2 Lists (names) And Finding Missing Values

Jan 23, 2014

I have a list of over 2000 names and need to establish which names from column A appear in Column B, as well as which do not appear in column B.

I attach an example : NameCheck.xlsx‎

View 3 Replies View Related

Excel 2010 :: How To Stop Macro From Sorting Hidden Rows

Feb 3, 2014

I recorded a simple Macro in Excel 2010 to sort data by the first Column, heading "Ref". I have assigned CTRL + e as the keyboard shortcut for this.

My sheet contains a number of hidden rows (as I hide rows when I have finished working on that issue).

When I run the macro, it does sort by the number of Column A, but also, the hidden rows are then shown again.

I realise I could just click on the A-Z sort button to stop this happening, but I really like being able to use keyboard shortcuts, which is why I tried the macro route.

I wondered if it is possible for the macro to ignore hidden rows?

This is the macro:
Sub SortByRef()
' SortByRef Macro
' Keyboard Shortcut: Ctrl+Shift+E


View 1 Replies View Related

XML File Conversion To Excel File Using Macro

Aug 16, 2014

I want to know how to convert XML file into excel.

I know using wizard method, if i use that table format alignment will be differing.

i required data should be in regular format.

i enclosed a file with flow.

xml file is not uploading so i written script in one sheet..

View 4 Replies View Related

Row File Numbers Missing

Jun 18, 2007

Excel 2003. As I entered 720 names/addresses in a spread sheet, I stopped and started repeatedly. When done, the row numbers go from 1 to 728. Puzzled, I carefully reviewed all names and the row numbers assigned. Row numbering skips eight numbers, 432-439. How do I get Excel to reassign the row numbers so they reflect accurately the number of entries?

View 2 Replies View Related

Sorting By Variable File Instead Of Specific Named File?

Jul 18, 2014

I recorded what I wanted but don't know how to generalise it so regardless of sheet name it can be sorted instead of specifically looking for "leanne final test" worksheet and sorting it by precise cells.

I have starred the section out so you don't have to focus on the rest of the code. Initally I ask for the file to be opened using WeeklyFN, would I need to use it again somehow ?

View 4 Replies View Related

Missing Sheet In Linked File

Jun 14, 2007

I have a summary file in which I capture data from multiple sheets in one existing file (targetfile.xls).

One month might contain sheets that do not exist next month. When updating (edit) links, Excel reports an error (invalid external reference) on the first missing sheet, and does not continue checking/updating links for the rest of the document.

I tried =IF(ISERROR( SUM('[targetfile.xls]sheet1'!$M:$M)),0,SUM('[[targetfile.xls]sheet1'!$M:$M)),

View 3 Replies View Related

Copy Missing Records From One File To Another

Jan 5, 2008

I am trying to copy all the records with condition TRUE as a cell value. I have more than 3000 records and 20 columns in two files. We have same data in both the files. My scenario is, I need to find the missing records in file 1 from file 2 and append those missing records to file 1. Similar work should be done on file 2 also. I have been using =ISNA function that returns "TRUE" for missing records. I need to copy those "TRUE" records to file1 from file 2 and also to file 2 from file 1.

View 6 Replies View Related

Excel 2010 :: How To Save Excel File As Cell Reference Using Macro

Mar 5, 2012

I have created a macro in excel 2010 which enable the file to save (extract) data into separate location and name. The vba code for macro is as follows: Question: How can I save this workbook with reference to the value containing in cell B2? (it is named temporary now - as defined in the code)

Sub aaa()
' aaa Macro


View 1 Replies View Related

Creating Customized Sequence In Excel

Apr 14, 2009

I have a table


As you can see, a,b and c is repeated times.

How do you make it so that it would look like




Is there a shortcut in doing this instead of copy pasting it?

I have a record just like this with about 572+ rows and 13 repeating items with that 13 items having their own column so copy pasting is really a tedious task. Kindly please help me solve this?

View 14 Replies View Related

Excel 2003 :: Macro To Open Folder Then User Selects File Then Macro Continues To Run

Nov 6, 2011

I use Excel 2003 at work. I'm looking for some code that will Open a folder and then lets the user to select a file then continues to run the macro.

The file name they select will look similar to this K2271011.504 or K3011111.201

View 3 Replies View Related

Combine 5 Macros In A Sequence Into 1 Macro?

Nov 28, 2012

I have 5 macros to be run in sequence. I have copied them into the file attached with ----- line as separator between them.

How can I possibly combine them all into ONE MACRO?

View 5 Replies View Related

Macro: Look At The Last Code In Col And Apply To Next Sequence

Dec 14, 2008

Step 1. In Col E a list of amounts will be pasted on a daily basis.

Step2. I need a macro that will look at the last code in Col F and apply the next sequence so $36,543.00 will have OPS003, since i might paste more than one amount in Col E i would like the macro to do the same thing also in Col F. I have a formula in G which will tell me what amounts are outstanding and which have cleared.

AmountCode 12,545.00 OPS001 1,236.00 OPS002 36,543.00

View 9 Replies View Related

Macro Instruction To Copy Down Sequence Of Numbers

Dec 24, 2013

I'm use to highlighting two cells, find the plus, and copying a list of numbers down about 10 rows. Is there a macro instruction or VB instruction that will do the same?






View 4 Replies View Related

Macro Sequence Controlled By Ribbon Button

Feb 4, 2014

If I have a macro sequenced:

sub test_1(control as iribbon)
call macro1
call macro2
call macro3
call macro4
end sub

I adjust the CUI editor to


I close everything down and save.

I open it up and click the button on the ribbon and it says it can not find the macro?

View 9 Replies View Related

Run Macro Sequence On Set Cell Changed By Drop-Down

Aug 25, 2008

I have found a macro that should work with a change of a specific cell.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$7" Then
Call Button
End If
End Sub

However, the code doesn t work as with a change of A7 nothing happens.
A7 equals to result from a drop down list --> A7=A5(which is a drop down list value). The "Button" macro is a combination of 4 macros that get rid of zero values and names in pie charts (maybe this plays any role). The master macro is assigned to an object. The code is:

Sub Button1()
ActiveSheet.ChartObjects("Chart 140").Activate
ActiveChart.SeriesCollection(1).ApplyDataLabels AutoText:=True, LegendKey:= _
False, HasLeaderLines:=True, ShowSeriesName:=False, ShowCategoryName:= _
True, ShowValue:=True, ShowPercentage:=False, ShowBubbleSize:=False, _
Separator:=" "...................

View 8 Replies View Related

Macro To Select Cells Based On Number Sequence

Feb 21, 2014

I have a sheet with hundreds of rows of customer information; including a 7 digit customer number. I need a macro to delete all the rows where the customer number sequence start with 1 thru 8 (that would be the first digit of the customer numbers).

View 9 Replies View Related

Copyrights 2005-15, All rights reserved