Taking Matching Fields And Numbering Them
I have a column where a lot of the fields match. What I want to do is set up a formula where all of the matching fields are numbered (1,2,3,etc). For instance:.......
I want the numbering to read the matching items and count them (not a sum but rather just a numbering/ordering column)
This is what I would want it to look like:...........
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Matching Name Fields
I have a list of company names and I need to find the duplicates. Of course pivot table finds the exact matches, but how do I find duplicates when only 1 character is off. Like punctuation or "s" or a mistype of a single character? Example: Jo Blo inc Jo Blo inc. Jo Blo inc Jo Blo icn Joe Blo inc Jo Blo inc
View Replies!
View Related
Matching Up Fields With Variable Input
Example: Order Number from VendorOrder Number from SupplierTracking Number from Supplier12345123451ZABCD13456134561ZBCDE134561ZCDEF14567145671ZDEFG1456714567 In these three orders, the first order nubmer has a 1-to-1 relationship with a single tracking number. In the second, there are two different tracking numbers associated with one number from column A. In the third, there is only one tracking number but three different lines of the same order number that need to be updated with it. The question is, is there a way to line up data fields head to head even when there are variable inputs like this?
View Replies!
View Related
Copy Matching Fields Of 2 Sheets
I want to filter data using two sheets. i want only the names in a field that are listed on BOTH sheets. i have a two lists of names and i want only the names that are included in both sheets while still including the rest of their information.
View Replies!
View Related
Populate Fields Based On Matching Numbers
I've been racking my puny brain for an hour trying to figure this out. Basically, my problem is with the last 2 invoices listed. If one invoice has "PO CLOSED" on it, I want all other invoices on that same PO to say the same thing. Surely I can do this with a formula or conditional formatting, but I cannot think of how. PO #Invoice #DateInvoice TotalDept.ActivityElementPO AmountPO Amount RemainingPO Status0707955289651/12/09$812.9075105854035$2,960.28$59.380707955459806/15/09$667.2475105854035$2,960.28$59.380707956486827/7/09$812.9075105854035$2,960.28$59.380707955584669/29/09$607.8675105854035$2,960.28$59.380719750913336/18/09$947.7975105853305$5,000.00$3,117.210719750915917/17/09$935.0075105853305$5,000.00$3,117.21PO CLOSED
View Replies!
View Related
Matching 2 Fields In 2 Columns And Copying A 3rd Field Into The 3rd Column
I need a formula. Probably Vlookup. I'm having a LOT of trouble trying to figure out how to "phrase" the formula so let me explain what it is I'm trying to do. If a cell in Sheet 1 Column AS and a cell in Sheet 2 Column B match exactly, I want the cell in Sheet 2 Column C that is in the same row as the matched cell in Sheet 2 Column B to be copied into Sheet 1 Column BB in the same row as the matched cell in Sheet 1 Column AS. For example, if cells in S1 Column AS and S2 Column B both have the name "LOS ANGELES" then "KLAX" will be copied from cell in S2 Column C (in same row as "LOS ANGELES") to S1 Column BB (in same row as "LOS ANGELES").
View Replies!
View Related
Taking Data From A Cell
Is there a fomula that would take data from a cell. I am looking to take everything before and includuing the word LTD For example if a cell contained Joe Bloggs Ltd (Mr Jones) C/o USA I would like to be able to take "Joe Bloggs LTD"
View Replies!
View Related
Taking Numbers Out Of A Number
what I have is a spread sheet of over 3000 numbers. Now this is what the number looks like: 0000123456000 what I need is basically a formula or something to take out the zeros (They are not really zeros on the spreadsheet, its just an example to make it easier to display). And leave the six digits in the middle. Is this possible?
View Replies!
View Related
Positive Cells Taking A Zero Value
I have a code below to only allow a number to be entered into one of 2 cells. I'm trying to do conditional formatting based on these to cells, and the evidence is showing that even when I type a value into either I21 or I22, they are taking on a value of zero. I have conditional formatting stating that if I21>0, then do one thing. And in a separate cell, I have =I22>0, then do another thing. Neither works, and even using =I22<>0, then do formatting, and it doesn't work, telling me that the assumed value is always zero.
View Replies!
View Related
For Loop Taking Too Long
I've got a sheet which I need to analyse and split into several different sheets but the raw data format leaves a lot to be desired as there are blank rows inserted randomly between rows of data. I need the 'good' data to stay in the same order so I've written a macro to sort through the data and delete any blank rows leaving the good stuff behind. The problem is this takes ages as there can be up to 30000 rows that need to be checked and I need to do this 5-6 times a day. I just wondered weather there was a quicker way to do this? The code I've got is detailed below: Sub Prep2() 'Delete all blank data rows Dim Rows As Double Dim Rownum As Double Application.ScreenUpdating = False Rows = Selection.SpecialCells(xlLastCell).Row For Rownum = 2 To Rows If Cells(Rownum, 11) "" Then GoTo NxtRownum Else Cells(Rownum, 11).EntireRow.Delete shift:=xlUp Rows = Rows - 1 NxtRownum: Next Rownum Application.ScreenUpdating = True End Sub
View Replies!
View Related
Using Loops For Taking Averages
I have a column of 96 numbers (observations every 15 minutes for a 24 hour period), and I want to take the average of these numbers in groups of four (the hourly average). My data starts in cell A2 and goes to cell A97. My first batch of averages are labeled as follows: Range("A2").Select startCell = ActiveCell.AddressLocal ActiveCell.offset(3,0).select stopCell = ActiveCell.AddressLocal Now, clumsy programming aside, I just want to say something like this averageCell = AVERAGE(startCell:stopCell)
View Replies!
View Related
Bank Reconciliation - Many To One Matching And Partial Cell Matching
programming 2 macros in a bank reconciliation sheet I am trying to create. Basically the data consists of two sets: the ledger side and bank side. Both sides consists of multiple columns that include date, description and amount. The following two situations can occur and which need to be covered with a macro. Many to one matching When we make payments to say 100 different suppliers at once, the ledger side will show 100 lines with the different amounts. However on the bank side it will only show one line with a total amount for the transaction. As the description on the ledger side is the same for all transactions done at the same time, it should be possible to have a macro add these lines and compare the total with entries on the bank side. Once a matching amount has been found, the macro should place an ‘x’ next to all entries on the ledger and bank side, in order to show that these transactions have been reconciled. Partial cell matching In this case we are only dealing with one line on both the ledger and bank side. The issue is that the description does not always perfectly match with one another. The ledger side might say ‘Brown Corp.’ and the bank side might say ‘Brown Corporation’. I want to come up with a macro that can recognize this partial match and still reconcile the lines by placing a ‘x’ to both the ledger and bank entry. The match however should be done with a combination of description, date and amount as several different bookings might be done for the same supplier in the same month.
View Replies!
View Related
Taking Number And Turning It Into Time
I have created a simple spreadsheet to keep track of work hours. I simply enter in each days hours and then I get a total. I have each cell formatted for time (hh:mm). However it is annoying to have to type in the colon for each days time. Is there a way I can just type in the three digits '9,3,0' into the cell and have it come out as '9:30'?
View Replies!
View Related
Taking Data From Other Spreadsheets Using A Macro
I have a number of reports in excel (all in the same format) which I need to take certain data out of and store it into one main spreadsheet. Searching through the forums I found some code which I think will do this: Sub test() Dim myDir As String, fn As String, ws As Worksheet myDir = "C: est" fn = Dir(myDir & "*.xls") If fn = "" Then Exit Sub Do While fn "" Set ws = Workbooks.Open(myDir & fn).Sheets(1) ws.Range("a7", ws.Range("a" & Rows.Count).End(xlUp)).EntireRow.Copy ThisWorkbook.Sheets(1).Range("a" & Rows.Count).End(xlUp).Offset(1) Workbooks(fn).Close False fn = Dir Loop End Sub I have tried using this code, altering the Dir String to the folder the documents are in. However, when I run the code, nothing happens. I don't get any errors, and nothing appears in the spread sheet. To be honest, I am not totally familiar with macros in Excel. I can get the gist of what the code is doing having using VB before, but am not totally familiar with some of the functions being used here.
View Replies!
View Related
Data Entry Taking An Age!
However data now takes far too long to enter; for example if I type the number 9 into a cell it takes around 30 seconds to let me enter more data, it is as if it is working on some calculations. The VBA I'm using is as shown below.
View Replies!
View Related
Taking Percentage Of The Larger Of Two Cells
I hope my title was clear enough, but I’m not sure how to set this up. I have numbers in cells B4 and B13. I want to take the largest of these two numbers and multiply it by 0.85. I then want to take this number and compare it to a number in cell B23. If B23 is greater than the larger of B4 and B13 X 0.85, I want B33 to display “No” in a boldfaced red font. If B23 is less than the larger of B4 and B13 X 0.85, I want B33 to display “Yes” in a normal black font. This formula will be in cell B33.
View Replies!
View Related
Routine Taking Ten Minutes To Update
Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Application.EnableEvents = False If Intersect(Target, Range("E:E")) Is Nothing And _ Intersect(Target, Range("H:J")) Is Nothing Then Exit Sub End If Is there a way to make this run a little slicker , as at the moment it is taking almost 10 mins to update RngDate is $L6:$FB1038
View Replies!
View Related
Taking Multiple Data Block To Another Sheet
I need to use Excel 2007 in a stock sheet situation. I have attached a picture of what the sheet will look like. What I need is a formula that will take a value in column c and then add the value in colum a behind that. Preferably with a X or - between them ie. If i took the first row and applied the formula it should kick out 5 x Jam The main thing is it must output the answer to another file/page so that the main page stays the same. And if there is no value in Colum c it must ignore it.
View Replies!
View Related
CALCULATION TAKING BUSINESS HOURS INTO CONSIDERATION
I need to do an hour calculation on two cells which have dates and times in both. the first cell is a call that we get from a customer and the second is the date and time in which that call is closed by us...meaning that call is complete. I need to calculate how much time in hours did it take us to complete that call for the customer. I need this calculation to respect our business hours of Monday to Friday 8am-5pm and closed on Saturdays and Sundays. here are some examples. from - 2/12/2004 13:00 (thursday) to - 2/13/2004 9:00 (friday) answer should be 5 hours from - 2/13/2004 14:00 (friday) to - 2/16/2004 10:00 (monday) answer should be 5 hours
View Replies!
View Related
Taking An Index Match Function One Step Further!
I'm matching ISO country codes to country names. This works great as long as the 'lookup_value' portion of the match fuction matches the 3 digit code on Sheet3. Is it possible to return my desired result without this matching perfectly. So a value in 'Sheet1 Column A' that begins A344xxxxxxx will return the same result in 'Sheet1 Column F' as if 'Column A' just had a 3 digit number.
View Replies!
View Related
Spreadsheet Taking A Long Time To Open
I have a couple shreadsheets I have created, one is for Purchase order's and has 4 different sheets. with one sheet holding addresses linked a drop down menu in the first sheet, all in the same workbook. Also there is a macro within this sheet to print to a specific printer, and a specific number of copies when CTRL p is pressed. The total file size is approx 9 mb. Opening this file takes approx 30 to 45 seconds. There are no links to any outside files, only links within the workbook itself. The second File I have recently started having problems with (Our Quotation log) is A workbook containing 3 sheets, 2 sheets independant and one sheet linked to the second that Summarizes the first and also has cels to enter information on the person the quotation has come from. I have this sheet formatted as a table so I can sort by name etc when doing followups. This workbook also takes approx 30 to 45 seconds to open, and on occasion longer. All workbooks are stored on my computer. The PO workbook has had the problem since I created it, although the Quotation log workbook has only had the issue this week.
View Replies!
View Related
Taking Area Codes Off Phone Numbers
I have a column full of 10 digit pone numbers. All different numbers and many different area codes (No dashes): Example: 3305555555 4583651586 4128563254 Is there a way to remove the three leading numbers from all of the numbers at once for the whole column?
View Replies!
View Related
Taking The Value Of A Range Of Cells From One Workbook And Place Them In Another
I've been trying to learn how to write code in VBA. I've been learning for about 2 weeks now (trying to learn it for work) and heres my problem. I have a macro that will create a pivot table from raw data that I input. From this pivot table, I want to go to the last row in the pivot table (the row that takes grand totals of each column of data). I want to write a macro that will take those values (located in a workbook called AR age sorting) and place them in cells in another workbook (called AR aging analysis). Basically cells D6-D9 in workbook "AR aging analysis" should equal the values of the last row in my pivot table in workbook "AR age sorting" (last cell with data in columns J-M). I thought this would be easy but I've run into some issues. Heres the code I use: Sub aging_summary() Dim Sheet1 As Worksheet Dim Sheet2 As Worksheet Set Sheet1 = Sheets("AR age sorting") Set Sheet2 = Sheets("AR aging analysis") For n = 10 To 13 For M = 6 To 9 Sheet2.Range(Cells(M, 4), Cells(M, 4)). _ Value = Sheet1.Range(Cells(n, 1), Cells(n, 1)). _ End(x1Down).Value Next M Next n End Sub I don't want it to copy and paste the values since I just need the value, not all the extra formatting and what not that is incorporated in the pivot table. When I try to run this I get an error message, saying that there is an application-defined or user-defined error.
View Replies!
View Related
VLOOKUP Performance Is Taking Very Long Time To Recalculate
I designed a spreadsheet which uses a lot of VLOOKUPs and it takes a very long time to recalculate. I don't know if there is another way I could do this, but this just seemed to make sense and it works just the way we want it but it just takes too long. Here is what the spreadsheet is designed to do: We have data that is pasted into excel from another source. The data is broken up into account number, date, check number, and amount. Multiple payments from the same account can occur in one month so the data must be totaled. There is a custom function called concatif which works just like sum if but concatenates text. There are VLOOKUPs performed on the account number and the corresponding date, check number, and amount are inserted in the table.
View Replies!
View Related
Concatenate Formula Not Taking Decimal Places/currency Sign
i have lets say a table with rows that contain the following data. SKU - PRICE - SALEPRICE - DESCRIPTION - SALEDESCRIPTION What I am trying to do, is to make a concat formula that says: SALEDESCRIPTION = DESCRIPTION+price was+PRICE+now+SALEPRICE In essence so that i can simply pull down the formula, and all our product descriptions have 'special offer, was xx (price) is now xx (sale price). This works fine, except that in the SALEDESCRIPTION, the amounts from the price and saleprice fields are being brought over without decimal places and without currency symbol. I have tried formatting those two columns as currency or accounting but neither works.
View Replies!
View Related
Random Numbering
I have a list of names in Column A going from row 2 to 15. I want to randomly assign them a number ranging from 1-14, but that random number can not be assigned twice. I only need each number once. I am putting the formula in column B.
View Replies!
View Related
Auto-Numbering
i have formulas in a range L5:L15 which sometimes return some value and sometimes zero. i want to give them auto numbers in column M in a way that it should only count the cell which has some value. suppose formula in L5 returns some value, L6 also then L7 & L8 have no value(but formula persists), cell L9, L10, L11 has values then L12 has no value L13, L14 has value and L15 has no value (but it has formula in it) values in these cells changes and some goes to zero and some return values. now i want to give them Auto Numbers in a way that cells with some value should only be considered.
View Replies!
View Related
Numbering In Forms
I have created a form to input parking ticket data to a spreadsheet, it all works exactly as i want it to, but i really need it to tell me the next available number or empty line, so i can use that for filing and audit purposes, ideally i would like it to do sequential numbering, but i've been looking for weeks and cant find a soloution, i have basic knowledge of VBA and i'm really struggling with this,
View Replies!
View Related
IF Statments And Numbering
Heres an example of what I'm trying to do, if I select form a list a certain name (i.e. "Plt") then I want it to populate a list of numbers (1-102) and the same with "SO" populating numbers 1-119. and here is what I have so far =IF(OR($F$1="Plt",$F$1="SO",$F$1="Plt LR",$F$1="SO LR"),"1.") Is there anyway of making excel do this?
View Replies!
View Related
Numbering System
Wondering if there is a formula for Excel that could replicate a numbering format like in Word? Example: A1.1. A1.1.1. A1.1.2. A1.2. A1.2.1. A1.2.2. A1.3. and so on... Idealy I would like to go farther than the 3rd level.
View Replies!
View Related
Numbering For Coordinates
how to get a single cell (C2) and (D2) to make the numbering format go from (## ## ##) to (######). The Excel spread sheet is a coordinate converter, designed to take Degree's minuets seconds and convert it to Decimal Degrees, the formula is set up and work Great, but every time I copy and paste the coordinate to the excel spread sheet, i have to manuelly erase the spaces between the numbers so the formula can work properly. How can i get the cell to automatically delete the space between the numbers to save me time.(I.e 29 35 42.34325 -to-> 293542.34325)
View Replies!
View Related
Numbering Macro
why the Macro below works fine when the spreadsheet is not filtered, but once you filter the spreadsheet it does not work. and if possible a solution. Sub Count() Dim MyInput As Integer MyInput = InputBox("Enter Start Number") MsgBox ("Start number is ") & MyInput mycount = Selection.Rows.Count MsgBox mycount ActiveCell.FormulaR1C1 = MyInput For Num = 1 To mycount ActiveCell.Offset(1, 0).Select ActiveCell.FormulaR1C1 = MyInput + 1 MyInput = MyInput + 1 Next Num End Sub
View Replies!
View Related
Sequential Numbering
I have a workbook with two worksheets. Worksheet #1 is a form that will be populated with data and saved as a new worksheet, then cleared and used repeatedly as a master form. Worksheet #2 is a log / register of the unique forms completed and saved from the master each time. I need to assign a unique sequential # to each form when it is saved and record this number in a column on Worksheet #2 (the Log). I am using some macros for the copy work but struggling with the auto-numbering of the forms when completed and saved.
View Replies!
View Related
Numbering By Group
i have items listed in groups and need to number them 1111 1111 1111 1222 1222 1222 1222 1444 1444 in the column beside this i need these items to be numbered 1 1111 2 1111 3 1111 1 1222 2 1222 3 1222 4 1222 1 1444 2 1444
View Replies!
View Related
Numbering A List
I'm trying to make a sequential resultlist starting with nr 1, 2, 3, etc under the column: Rank ? This should be part of a macro, so autofill is not an option... As you can see, the number of rows are different from each group, and starts with nr 1 for every group. (Some formatting became all wrong posting this.........
View Replies!
View Related
Sheet Numbering
I'm wondering if this is the way things work and there's nothing to be done about it (but I doubt that). I have a workbook that I load data into from a csv file. The csv file is "divided" into regions, and I want each region's group of data to be loaded into a separate sheet. To be on the safe side, I delete all the sheets before loading the data with the following code that I found in this forum Sub delete_all_sheets() Dim sh As Worksheet Application.DisplayAlerts = False For Each sh In ActiveWorkbook.Worksheets If sh. Name <> ActiveSheet.Name Then sh.Delete End If Next Application.DisplayAlerts = True End Sub Then, for each new region, I create a new sheet with the following code On Error Resume Next sheet_nr = sheet_nr + 1 Sheets(sheet_nr).Activate If Err.Number <> 0 Then ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count) End If On Error Goto 0...............................
View Replies!
View Related
Restart Auto Numbering
I have just successfully added a code to Visual Basic in order for it to insert a sequential number automatically upon opening the worksheet. It works great, but how do I restart the numbering now that I know it works?
View Replies!
View Related
Auto Numbering A Tag Name
I would like to know if there is a way to Auto number a text. I have a column with text tags (lets say Column B). These cells look at a specific cell (ex. A1) and see what text is written in it then copy the text into their own cells B1, B2, B3 and so on. So if cell A1 reports AAA then Column B cells become AAA all the way down. Now what I like to do is for column B cells look at A1, copy the text and add _01 infront of their copied text. so for Column B, B1 reports AAA_01, B2 is AAA_02, B3 is AAA_03 and so on
View Replies!
View Related
Insert Bullets And Numbering
Is it Possible to Insert Bullets and Numbering in Excel. Especially Bullets And what is the Easiest way to insert Bullets. Sheet2 ABC1ItemsWant this2BindersØ Binders3Pen SetsØ Pen Sets4PencilsØ Pencils5BindersØ Binders6BindersØ Binders7PenØ Pen8BindersØ Binders9BindersØ Binders10BindersØ Binders11PenØ Pen12PencilsØ Pencils13DeskØ Desk14PencilsØ Pencils15BindersØ Binders16Pen SetsØ Pen Sets17BindersØ Binders18BindersØ Binders19PenØ Pen20Pen SetsØ Pen Sets21PencilsØ Pencils22PencilsØ Pencils23BindersØ Binders24DeskØ Desk25PencilsØ Pencils26Pen SetsØ Pen Sets27BindersØ Binders28PenØ Pen29BindersØ Binders30PencilsØ Pencils31BindersØ Binders32PencilsØ Pencils33PencilsØ Pencils34PenØ Pen35Pen SetsØ Pen Sets36BindersØ Binders37Pen SetsØ Pen Sets38PencilsØ Pencils The "Ø " Indicate Bullets that means " Ø in Column C
View Replies!
View Related
Auto Consecutive Numbering
I have a form that I use often, but numbering is slow because I go in and number the form, print, go back and put in next number, print, etc. Is there a macro or formula that will automatically update the consecutive numbers when I enter or print?
View Replies!
View Related
Quick Auto-Numbering
Auto-Numbering just an example:- 56 57 58 59 60 The Column above is the first column on a selected sheet. i will select 56 and from there (End-Shift+Down arrow) which selects all the values from 56-60... My question is from here on if there is a shortcut key or 'vba macro' that can autonumber from 1. Thus giving output result of.. 1 2 3 4 5 i want to record the solution for above problem in a macro recorder for different numbers that is why i have to do (End-Shift+Down arrow)
View Replies!
View Related
Automatic Numbering In VBA Etc
I'm trying to create a bug reporting tool (with a bunch of text boxes and drop down lists) and have the following problems... 1. I would like to get a unique number inserted automatically in a textbox (it's supposed to be the bugs id (1001). How do I do this? And when I click OK after inserting all info I want this number to become +1 so the next defect can be added immediately. 2. Why are my drop down lists empty as default and their values only appear if I enter a value. Why aren't the lists displayed when i just click on them? 3. I have a multipel row text box. How do I get the text to jump to the next row automatically instead of using crtl + enter?
View Replies!
View Related
Numbering Blocks Of Data.
I have 250000 lines of data and at the moment they are in seperate blocks of different sizes, and seperated by 5 blank lines. For Example 112 1523 523 1523 *5 BLANK LINES* 12 23 *5 BLANK LINES* 344 4563 etc. What I would like to do is give each block a number. 1 112 1 1523 1 523 1 1523 *5 BLANK LINES* 2 12 2 23 *5 BLANK LINES* 3 344 3 4563 The lines in between will come out eventually I just need them there as they are difineing the blocks of data.
View Replies!
View Related
|
TRACKER
