Sort The Data :: By TAG And PA Claim
I have a monumental amount of data that needs to be sorted. The data is sorted by TAG number and then by the claim type EG: PA or TA and so on. There are 8 possible claim types and Each TAG may have only 1 EG: PA or it may have PA, TA, BC and so on.
The thing i need to do is sort the data firstly by TAG and PA claim, then instead of having the tag number again below the PA claim type, and having the same TAG number again and next column being a TA type claim, i want to have all CLAIM TYPES IN ONE ROW......
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
VBA Code To Sort Data - Run-time Error When No Data To Sort
I havet he following code which sorts data. If there is no data to sort I keep on getting a run time error. Could I add something to my code to prevent the run-time error, as sometime there won't be any data to sort. The code runs when I switch to the worksheet in question. Sub SortMeetings() Dim iCTR As Integer Dim yCTR As Integer Dim zCTR As Integer zCTR = 11 For iCTR = 12 To 23 For yCTR = 1 To 10 If Len(Range("D" & iCTR).Offset(0, yCTR)) 0 Then Range("AA" & zCTR).Value = Format(Range("D" & iCTR).Offset(0, yCTR), "HH:MM") & " " & Range("D" & iCTR).Value zCTR = zCTR + 1 End If Next yCTR Next iCTR Range("AA11:AA" & zCTR).Select Selection.Sort Key1:=Range("AA11"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub
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
Create Tag In VBA?
I found this really cool code that creates a tag cloud in Excel using VBA. It's far too complex for my understanding but I was hoping I could get some of the experts opinions on here about modification possibilities. I have attached the macro-enabled 2007 workbook. What I am trying to do is search through a list of keywords and determine each keyword's density within a list. The list for example could look like this: slow windows xp windows xp running slow windows xp computer [slow windows xp] [windows xp running slow] [windows xp computer] [computer running slow windows xp] "computer running slow windows xp" "slow windows xp" "windows xp running slow" "windows xp computer" You'll notice the characters " [ ] of which I would like to ignore when the cloud builds. The cloud would list the most dense keywords first, and gradually decrease to the least dense keywords. The macro code is inside the workbook but I'll list it here to:
View Replies!
View Related
Formula To Change The Tag On The Other End
I am making several thousand wire tags for a water treatment plant which need to be installed at each end of the cable runs. What I have made so far is tags for one end of the run not the other. I am looking to create a formula to change the tag on the other end so that instead of it saying 1234 to 5678 it would now say 5678 to1234
View Replies!
View Related
Set Control Visibility Based On Tag Property
I have a userform which uses the tag property of the frame to determine whether a frame is visible (and hence the controls that reside within the frame). A frame contains three combo boxes, and six text boxes. The tag property of the frame matches the number of frames that are visible on the form, so that if the user selects five frames, frames 1 through five become visible and for all other frames visible = false. A frame contains all the data for a single entry. There can be up to fifty frames/ entries that are visible on the form depending on the user selection. I want to use the visibility property to do two things: first the combo boxes are filled from an array after the user selects the number of frames (or entries). I only want to fill the comboboxes where the frame is visible. The second thing is that I have a function which uses the data from the text boxes and combo boxes as required arguments. Since these are required arguments, I will get a data mismatch error if I try to call it and the controls are empty. Therefore, I only want to call the function if the frame which houses the controls is visible. Public Sub Visible1(Entry As String) Dim ctrl As Control For Each ctrl In UserForm2.Controls If TypeName(ctrl) = "Frame" And ctrl.Tag <= Entry Then With ctrl .Visible = True End With Else If TypeName(ctrl) = "Frame" And ctrl.Tag > Entry Then With ctrl .Visible = False.............
View Replies!
View Related
Read & Write Mp3 File Tag Properties
The problem with the Write macro was that, although it did work, it used SendKeys which has to be slowed down considerably. Here is a new version of the Write macro that works as normal. I originally tried to use CDDBControl.dll version 1.2.0.51 which is widely available on the internet but found that I could only get it to change 1 file before crashing Excel. I have put the READ macro in the next message. Dim ws As Worksheet Dim FromRow As Long Dim LastRow As Long Dim FilesToChange As Integer ' number of files to change Dim FilesChanged As Integer ' number of files changed Dim MyFilePathName As String ' full path & file name Dim MyFileType As String ' mp3 wma etc. '- Dim id3 As Object Dim MyArtist As String Dim MyAlbum As String Dim MyGenre As String Dim MyTrack As String Dim MyTitle As String Sub WRITE_TO_EXPLORER() Application.Calculation = xlCalculationManual Set ws = ActiveSheet Set id3 = CreateObject("CDDBControlRoxio.CddbID3Tag") LastRow = ws.Range("A65536").End(xlUp).Row ' count worksheet rows FilesToChange = ws.Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible).Count If FilesToChange = 0 Then MsgBox ("No files to change."): Exit Sub FilesChanged = 0........................
View Replies!
View Related
Sumif; Find The Amount Spent Based A A "tag"
I have a spreadsheet(see attached) that I am needing to find the amount spent based a a "tag" I have created. If you bring up the spreadsheet and look at the dashboard sheet, I am wanting to find the total tag amount based off what is listed in the paychesk sheet. For tests I have used this formula and have put it in B6 on the dashboard sheet. =SUMIF('PayCheck - DEC-09-B'!$F$2:$F$1000,A20,'PayCheck - DEC-09-B'!F$2:$G$1000) But the "bank amount" field on the paycheck sheet is a negative number the value of course coming back is not right. How do I fix this?
View Replies!
View Related
Sort The Data..?
In the attached version the "Print Version" tab sorts the values entered into the "Log Entries" tab. When entries are deleted from the "Log Entries" tab they are converted into 'blank' entries and moved to the end in the "Print Version" tab. However, in earlier versions the "IFERROR" does not appear to work, therefore any blank entries are shown as "#NUM!", which is kind of annoying. If there any way around this? The forumla is shown below. =IFERROR(SMALL('Log Entries'!$A$1:$E$47,ROW('Log Entries'!A1)+COLUMNS('Log Entries'!$A$1:A1)*46-46),"")
View Replies!
View Related
Sort Rank Data
I have a table that goes from columns A-AF and Rows 5 - 18 In column A I have a name and in AF I have a Rank based on the results of other factors and calculation in colums B-AE. I would like to ahve another table under my current table that will sort the rank is order 1-14. and have the store and the place it is rancked in order. I was hoping it was a simple Vlookup formula but I cannot seam to make it work.
View Replies!
View Related
Data Sum And Sort
to use a Macro in order to batch process a set of data that will have consistent columns, but may have additional rows. I have attached the file, three tabs. 1st - Sch of Inv(2): The intended goal The data is sorted first by Georgraphic Location (A), then Strategy (C), then Substrategy (E). Column F should be the same formula that is currently there, but updated for column I once the information is pulled (I""/$F$29*100). The *100 is to only display the % symbol once. If there's a better way to calculate percentages and only show the symbol on the first one, that would also help. Column I is the place to calculate the data from Tab 2. Column K is your check figures, which are within $5 (due to a miskey probably, not important). Cell F29 will be a manually hard-coded number on each sheet (which would be entered before the Macro is initiated). 2nd - Cost Query: The data source sheet 6 columns, 5 have relevant data. Manager shortname is the 'primary key', but won't need to be displayed anywhere. Region-Strategy Code-Substrategy Name correspond to (A), (C) and (E) on Tab 1. The goal is to sum by each of these factors, then display the one sum on Tab 1. The pull will include column F, but it has no actual value. I'd prefer to be able to just keep it on Tab 2. 3rd - Pivot of Cost Query: The step inbetween? Here is the proof that the data can be easily pivoted to provide all of the values, but I'm having trouble figuring out how to quickly move those values into the predetermined format of Tab 1. Questions: Is there something simple that I'm missing to move the data from the pivot into the predetermined format? How can I use a macro to take the data on Tab 2, move it into a pivot on Tab 3, then move that data to the format (column I) in Tab 1? Is this process easier without the pivot step in the middle?
View Replies!
View Related
Macro To Sort The Data
I need to sort the following data by the CODE number shown while keeping the Description and Quantity attached to the various codes. (Excel is smart enough to first sort the codes numerically then alphabetically, which is what I need) The client info (bottom) also needs to be left alone. Done correctly, after running the macro, the order would be 04cls, 04fba, 08jud, with the Desc. and Quantities moved with the codes. At the moment each line is stored in a single excel cell. Description = Item1 Code = 08jud Quantity = 1 Description = Item2 Code = 04fba Quantity = 1 Description = Item3.........................
View Replies!
View Related
Sort All Data In EACH Worksheet At Once
I have a workbook with about 30 worksheets (I know not that many, but enough to make me want to automate the following) They all have the same columns and headers. I want to sort them ALL based on Column E for example. Is there a way to do this easily, with out having to "manually" sort each worksheet?
View Replies!
View Related
Bulk Data Sort
Is there a simple method to bulk data sort? Explanation: I run a workbook with worksheets in calendar months (12) These consist of names in alphabetical order. If a new name is added to or deleted from all the sheets (I've cracked how to do this) how can I data sort them without doing it sheet by sheet?
View Replies!
View Related
Sort The Rows Of Data
We have an issue with sorting rows of data that have formulas in them. --------------- These formulas are using data from a row which is above the column headers. For example cell H2 has 160 manually entered in it. It's the number of work hours available in January 2009. The column header for that column is in Q4 and has a value of 'Jan' Below this in cell H5 is the formula '=+H2' because the resource in this row is available for 100% of the month. Another resource in row 22 has the formula '=+H2*0.15' in cell H22 because they are only available 15% of the time that month. --------------- The problem occurs when we try to sort this data. Most of the cells displays #REF!. Some show a value, but the calculation is wrong.
View Replies!
View Related
Sort Data Using Two Criteria?
I have multiple employees working on different projects. I would like to use a formula or some kind of macro where I can automatically scan an entire worksheet and manipulate the data to show me how much time was spent by each employee on each project. Using the SUMIF command only lets me use one criteria. In my case I need to sum if in the row there is a match with the employee name and the project name. For example, if Employee A is working on Project 1 then sum the units. I would like everything summarized in a nice table. Attached is a basic example of my sheet.
View Replies!
View Related
Auto Sort Of Data
As you can see from the attached spreadsheet. I have a page called Pattern Etch where we enter the data for all the processing done in a single machine. Each device type is slightly different, and therefore has a different calculation fasctor for its etch time. This all weorks fine. What I had previously was another spreadsheet with the pages b and g in it. This is where the factors are calculated and updated from. I'd like to make this update automatic, and suspect the easiest way is to combine the two spreadsheets, as in the attached version. Can the sorting of the data from the patternetch page to the apprpriate pages, b and g be done automatically? If so can it be done for many pages as I have cut down the spreadsheet, I actually have 50 variants, each will need its own page (as for b and g).
View Replies!
View Related
Sort With Data Validation
I've found that sorting a column next to one which has drop down boxes set up in it, does not take the options for the drop down boxes (data validation ) with them when being re-arranged. Is there a way to make this possible, or an alternative format I should be using? see example: If item 4 is the only one available from supplier 3, (As shown by the drop down box options) how do I sort by say, department, and still have the correct options on hand? Sorting by department, the items move, but the data validation info stays where it is, leaving the supplier 3 option for item 1, which is not correct.
View Replies!
View Related
Sort Data On All Worksheets ..
I have a worksheet of about 75000 rows so I have to use 2 worksheets. I want to sort the data in column A in ascending order so I want to start at 4999 until whatever number happens to be in row 65536 then continue sorting the next highest number in another sheet. I tried using this but it didn't work: ..
View Replies!
View Related
Sort Data With Code
I have a set of Data and I would like to order the data based on a column which is G and contains Dates. I would like to order the data Desc order but this must be done in VBA as its a monthly report and needs to be scheduled.
View Replies!
View Related
Automatically Sort New Data
I have (what seems to me) a somewhat complicated request. I’m trying to do a custom workbook without using VB (if possible). I’m trying to design a file list that can be updated easily by anyone. The new row of information needs to be entered (by a data form maybe?) and then automatically alphabetized by ‘ case name”. See below. I don’t know Excel formulas that well. This seems fairly easy, but I am at a loss how to do it. Case NameCase #Open Date Closed DateRep 1Apple, Aaron2006-45 12/8/06 Tom 2Apple, David2004-02 01/14/04 Bill 3Bennett, Tim2006-0411/28/06 Bob 4Carter, Jimmy2005-23 04/22/05 Chris 5Carter, Tim2000-11 08/02/00 Jim 6Ford, Gerald 2007-206/06/05 Jill 7Gold, Bill 2006-7803/12/04 Pat 8Hill, Jim 2006-7703/18/04 Amanda 9Janes, William2005-6803/14/05 Dave I would like to enter a new record (“case name -e.g. Jones, Smith, etc.) , with all the other corresponding info (Case #, Open Date, Rep #) and have excel automatically insert it ALPHABETICALLY by case name into the sheet and also, if possible, renumber. I know I can add the new record it to the last row and the Sort by case name, but I wanted to have others do it, and when they try to enter and sort the spreadsheet it always gets messed-up.
View Replies!
View Related
Data Sort By Header
what is the code to sort a spreadsheet in VBA code by HEADER rows? i have 2 rows i want to sort by: first by Column D row 1, then by column C row 1. Cells.Select Range("C1").Activate Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Key2:=Range("C2") _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _ :=xlSortNormal Range("C2").Select selects all cells, sorts, then de-selects.
View Replies!
View Related
Pivot Table To Sort Data?
I need to format some information in an Excel file, before exporting it. All the pivot table information I have found online is primarily for account management, using counts and sums. The data is currently in this format: DEV#WIRE#SWITCH-1A123AMP-1A124AMP-1A125AMP-1A126SWITCH-2A127 I need to sort it into this format in another sheet or another area in the same sheet: DEV#WIRE#1WIRE#2WIRE#3AMP-1A124A125A126SWITCH-1A123SWITCH-2A127
View Replies!
View Related
Transferring Data/Auto Sort
I am trying to combine rows of data, each 4 cells long from multiple worksheets into one master sheet. I need to know if there is an easy way to do this and also I would like to setup an Auto Sort if possible to automatically sort the rows by date. The data that will be transferred is: Date, Amount, Description, and Company Billed. I have my workbook organized by a Vendor per worksheet and would like to have the data I enter inside the vendor worksheet to automatically transfer to the master sheet and sort by date.
View Replies!
View Related
Macro: Sort Data Around Selection
how to sort a selected range. The end of the range could be any cell. The top of the range is fixed in A1. I've tried with this: Sub Order() Dim final As Variant final = Selection.End(xlUp).Address Range("A2").Select Range("A1:" & final).Sort Key1:=Range("C2"), Order1:=xlAscending, Key2:=Range _ ("G2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _ :=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _ DataOption2:=xlSortNormal End Sub
View Replies!
View Related
Data Auto Sort By Date
I have a very simple excel with three columns Name (column A), Date (column B), Hours (column C). I want the excel to autosort upon opening so the data is sorted by date. I right-clicked on Sheet 1 and put this in (I found it online): Private Sub Worksheet_Activate() Application.ScreenUpdating = False Range("B1").CurrentRegion.Sort Key1:=Range("B1"), Order1:=xlDescending, Header:= _ xlGuess, OrderCustom:=1, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Application.ScreenUpdating = True End Sub
View Replies!
View Related
Sort Data Over Multiple Columns
I'm trying to figure out a way ( excel 2000) how to sort data over a range of columns. Attached is a sheet. So what I am looking for is this: Bottom 10 for target 1, target 2, target 3. I can sort them indervidually, but is there a way to sort the all? Or would I need a agent column for each target to sort? Or maybe there is a way to sort the data so it would work out that if they are in the bottom 10 of lets say 2 of the targets but not all 3 they would still show in the bottom 10?
View Replies!
View Related
Sort Data On Multiple Worksheets
I am using the code below to transfer data from a single sheet to approx' 200 sheets. These sheets are staff training sheets, one per staff member. This code works great. What I would like to know is, is there a way to then sort the data on these sheets in decending order? I have tried on sheet change but this seems to stop the transfer to other pages. Sub Tranfser() Dim shtTemp As Worksheet Dim lngOutRow As Long Dim rngData As Range For Each rngData In Range("A5", Range("A5").End(xlDown)) Set shtTemp = GetWorksheet(rngData.Offset(0, 1).Value) If Not shtTemp Is Nothing Then ..........................
View Replies!
View Related
Sort The Data :: Count The Zero Values
In a column I have loads of numbers like this except there are 5000 of them. 3 2 0 0 0 0 2 0 3 0 0 2 0 9 how could i sort it into another column so it stays in the same order but counts the 0 values so it would look something like this. 3 2 0 (x4) 2 0 (x1) 3 0 (x2) 2 0 (x1) 9
View Replies!
View Related
Sort Data. And Automatically Fills
Scenario: You have two columns in a spreadsheet. Column A is a text input and column B is a tracking number for each row. Using the ISTEXT statement, I had it configured so that when a cell in column B recognized a TRUE statement in an adjoining cell in column A it would then assign a number. B1 is assigned a number when A1 has text. What I would like to happen is build the spreadsheet so that after the user enters text in column A, column B automatically fills in so that the user doesn't have to manually enter it. I've figured out how to do this if the data is never sorted, however, if you sort the data then the tracking numbers don't follow and are really a mess. Is there a way to auto fill column B and at the same time, make it so that the tracking number that is originally assigned to the row follows with A when sorted? I've tried using =MAX but run into circular reference issues.
View Replies!
View Related
Sort Table After Data Update..
I have a spreadsheet with 3 tables (all side-by-side). Table one has 8 rows of data; table two has 2 rows of data; table three has 3 rows of data. I am looking for assistance on how to sort each table in desceding order (by sales). Basically I want to do a sort everytime there is a change of values in any of the tables. I have attached a sample spreadsheet to clarify the table layout and the manner in which the values change within each table.
View Replies!
View Related
Sort Data Into Multiple Rows
I'm trying to merge 2 spreadsheets togeather but befor I can do this I need to resolve the following issue - my Excel worksheet has 2 columns with data that looks like this Value1X1,X2,X5 I need it to change to this Value1X1 Value1X2 Value1X5
View Replies!
View Related
Sort Data To Multiple Worksheets
We are trying to sort a spreadsheet by the data in column I. This column refers to a state. I need help creating a macro that can sort column I so that different states go into different worksheets. States ME, NH, MA, RI, CT, VT go to a worksheet titled 357899, states NY, NJ would go into worksheet 351835, states MI, IN, OH would go into worksheet 351857, and everything else would go into worksheet 351836. The main data worksheet where the info is being sorted from is named All_Accounts. Column I has a header labeled State, so data actually starts in Row 2. I need the full rows copied to the new worksheets while leaving the main All_Accounts worksheet in tact.
View Replies!
View Related
Sort Data With Two Header Rows
I have two header rows on a sheet that I need to sort starting on Row 3, Column A. The sheet is A-K and rows 1 and 2 are headers Row 1 has merged cells of A-F which says "Information" and then G-K " Tracking Section" Row 2 is broken up into individual sub-headers in columns, A-K each one having a bit more info for instance: Under the "Information" merged header in row 1 there is A2 Name B2 Work Center C2 C/O date D2 Due CC E2 Due MX F2 Date Received and G-K in row 2 has different sub-headings. I have tried to use my normal code, which is great for one header, but it ends up sorting the sub-header as I call it...
View Replies!
View Related
Sort Data On All Worksheets Active And Other
It sorts the ActiveSheet, but none of the other sheets and there's no runtime error. I am using this on a test workbook with the same data in 5 worksheets. What's wrong with this code? Sub SortSheets() Dim ws As Worksheet For Each ws In Worksheets Cells.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _ xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Next ws End Sub This looping structure works for PageSetUp, but not this Sort.
View Replies!
View Related
Sort Range Data Blocks
I have a list of data (as shown below under Input), which I would like to sort them according to their block number (as shown below under Output). The hurdle is the sorting should be able to cater for missing fields. Also, note that the sector can be recurring, that is "P,P,Q,Q", and then follow by "P,P,Q,Q" again. INPUT Block No.SectorData 11P19 22P 33P39 41P18 52P21 63P38 75P22 85P24..............................
View Replies!
View Related
Sort Data Spread Over Two Sheets
I have two sheets within one workbook, mean I have 300 columns, as u know excel sheet is limited to 256 columns, so I divided the columns in 2nd sheet, now I want to sort the data, but when I sort the first sheet data, the second sheet data not sorted,
View Replies!
View Related
Sort A Table Of Data That Changes. Is Dynamic
am working on a spreadsheet that requires sorting a list of names and numbers. The numbers have a sum formula attached. I can set up the macro to sort the list fine by selecting the appropriate cells and creating the macro. The code for this is below Sub SurnameSort() ' ' SurnameSort Macro ' Sorts by mechanic surname ' ' Range("A10:H13").Select Selection.Sort Key1:=Range("B10"), Order1:=xlAscending, Header:=xlGuess _ , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub However, I want the macro to still be able to sort the data when I add new rows to the list later on. In other words, the code above in bold somehow needs to be modified so that "H13" extends as far down as needed.
View Replies!
View Related
Sort And Subtotal Part Data
I have data with two types of records: those with "orders" in teh comments field and those with something else in that field. I'm able to sort on comments so "orders" records appear at the bottom, and insert two blank rows, separating the two datasets. Now I have to be able to set the range of the top portion. I worked on the problem, and used some of the code in the response to my post. I need to store two cell addresses. To determine the range, I find the first blank column, then move one cell to the left, then I need to store that column's letter. AFter I find the first row with "orders" in the comments, I need to insert two blank rows and move back up to the row above that containing data (this much I've done), then store that cell's row's address. I need to use the column and row I record as the range to select for the sort. So, I basically need to things: 1. How to store the active cell's column and row 2. How to stop recording, type in code, then resume recording.
View Replies!
View Related
|
TRACKER
