Sorting Based On Category And Username
Mar 25, 2008
I have imported a list of information from a text file created by my network admin. I need to take the information from different categories in the list and sort them by category. All of the information is in once cell, but each has a common prefix (ie. URL = or Username), etc. After each prefix is the information I'd like to sort under the category (each category is the prefix). All of the information (prefix & data) is in cell A because it was a text file, and I'd like to simply find a way to say if A10 = URL= then place the information in C10...Below is what I need to sort.
URL = [url]
User Name =
Source IP = 0.0.0.0
Destination IP = 12.345.67.89
Disposition = CATEGORY_BLOCKED
Lookup Code = WISP_URL_BLOCKED
Category = Proxy Avoidance
Elapsed Time = 0 ms
AVG TIME PER REQUEST = 0 ms
------------------------------------------
Sending URL_LOOKUP_REQUEST_EX...
------------------------------------------
URL = [url]
User Name =
Source IP = 0.0.0.0
Destination IP = 123.456.789.101
Disposition = QUOTA_USER_BLOCKED
Lookup Code = WISP_URL_BLOCKED
Category = Social Networking and Personal Sites
Elapsed Time = 0 ms
AVG TIME PER REQUEST = 0 ms
View 9 Replies
ADVERTISEMENT
Oct 5, 2007
I have a thousand names on a column, and I have a several worksheet tabs as locations, such as CA, AZ, TX, and NY.
All names goes to main worksheet, show like this
apitchford 10 100 123
bkishpaugh 9 211 123
blee 14 234 111
cbonny 21 125 412
I need to pull a specific name, example, name = blee, move that name with all data on that row into TX.
I will need to set as automatically, becuase the names add and delete on the main worksheet, and will auto update those tab worksheets. (make sense?)
I have tried INDEX and MATCH, but they keep putting one data in, not all data.
View 9 Replies
View Related
Sep 18, 2013
Basically, I have a table of data in Excel 2010, akin to the demonstration below (just much, much, MUCH bigger).
Category
Genre
Title
Author
Year
[Code]....
View 2 Replies
View Related
Jun 15, 2007
Tried to explore with sumifs but failed terribly.. does anyone know a formula that can sum up figures in different columns under the same Category.
Have attached a sample xls..
View 5 Replies
View Related
May 20, 2013
I have a button that saves my excel workbook to a network drive, but what I'd like to do is have it save the file to a directory named after the user, instead of all the users in the department saving to one folder. For instance, R:customerserviceweight reconstructionusername, where username a directory named after the user.
Code:
Private Sub Save_Click()Dim Path As String
Dim FileName1 As String
Dim FileName2 As String
FileName1 = Range("A2")
FileName2 = Range("A3")
ActiveWorkbook.SaveAs Filename:="R:CustomerServiceWeight Reconstruction" & FileName1 & " - " & FileName2 & " - " & Format(Date, "yyyymmdd") & ".xlsm", FileFormat:=52
End Sub
View 1 Replies
View Related
Dec 14, 2006
Sheets("sheet1").Range("a1").Value = Environ("username")
If Range("a1").Value = Range("H5").Value Then format.cell("c24")Protection=unlocked
I will have someone type in their supervisor's name in H5. When they send the form to their supervisor and A1 matches the entry in H5, I want cell c24 to be unlocked so the supervisor can make an entry.
View 9 Replies
View Related
Jan 30, 2014
I have 2 tables:
IDTypeValue
1A3
2A3
3A4
4B4
5B3
6C2
7A4
8A3
And
TypeTotal
A
B
C
I need to "total" column to automatically display the sum of the values for the categories A, B and C. Is there a formula I can use for this?
View 2 Replies
View Related
Mar 6, 2009
The goal here is for users of this form to enter the "type" of tool and automatically create a list of tools based on the "type" in another sheet (NEW SHEET). See the heading on that sheet in the file for more info.
Also, keep in mind that this would be a template (.xlt) for 2003 and all the sheets would be individually locked -- password protected.
Since this form will be filled in by folks that aren't very computer savvy in general, I'm trying to make this as simple/automatic as possible. I myself have a limited toolbox as well. I'm not entirely sure a program would be needed (IF statements in the cells?)
View 3 Replies
View Related
May 24, 2005
I am trying to format the colour of a column chart based on the value of the x axis (not the y axis value). Firstly I need to know if it is possible. Secondly I would appreciate any hints / VBA code that could be useful.
View 4 Replies
View Related
Nov 1, 2011
I have spent a good amount of time trying to create the below using botched IF functions, SUMIFS, SUMIF formulas etc.
Basically I have a sheet (called Sheet1) with the following columns:
A - Date (by individual day DD/MM/YYYY)
B - A catagory (so using the old example, Apples, Orange, Pears)
C - Price.
I would like to be able to create a formula, whereby I can sum the Total sales for each catagory across each month onto a summary sheet (sheet2).
I would use pivot tables, however am not sure how i can make all the require data appear permanently in the best way, especially if there is a one liner that can be done more easily. So far I have managed to create a sum dependent on month, but have been unable to encorporate the catagory.
View 1 Replies
View Related
Apr 27, 2014
I need new workbooks based on category name in the below list. Respective sheets should be copied from the existing workbook with items as sheet names. List can be updatable.
category(workbookname)Items(sheets)
Vegetables carrot
vegetables brinjal
meat chicken
meat Fish
meat Mutton
grains Wheat
grains Rice
View 4 Replies
View Related
Dec 2, 2009
I have 3 rows to each customer. I need to sort according to column B, by date. However I need to keep all 3 rows togeather. Is this possible?
View 6 Replies
View Related
Jul 26, 2012
I have a worksheet where users enter an inspection number based on the order a property will be visited, based on the inspection number I want it to automatically enter the information into another worksheet that creates an itinerary which shows this order .
View 4 Replies
View Related
Dec 30, 2013
I have a below challenge in front of me. When ever the user inputs a value from source, that value should be first in the destination and the rest of source values should be sorted as below. . Below are examples
Source
Input Cell
Output
abc
abc
abc
xyz
efg
[code]....
View 5 Replies
View Related
Aug 22, 2008
I understand how to sort based on Ascending order and descending order but what if I have to sort based on cell value? For example, in Col. 'L' I have a "Type' header name and under that there are probably 10 different categories. Few of them are "Achieved", "Bypass" "Certifications", "Monitor",and "YTD" . I would like to know how can I sort all the rows with "Certifications" first and then comes rest of the data and finally at the buttom of the data is "Monitor".
View 9 Replies
View Related
Jun 2, 2014
Here is the data i'm working with.
Name of player
Score of round
bag tag the player came in with
bag tag the player is leaving with
Here is what I'm trying to accomplish. I usually just copy and paste (then sort the data) but I want a spreadsheet that I can enter raw data and just look at the results I will already have the players names in my sheet. Before the round I'll get everyone's tags it will not always be in order (EX: 1,3,15,22,34,66...etc) some tags will always be missing. After the round I'll enter the scores. What I can't figure out is how to leave my sheet in alphabetical order and have my spreadsheet populate the "leaving tag" column with the correct tag based on the players score....
name score tagin tagout
bob 22 2 2
carl 44 15 23
ed 33 23 15
Can't tell if that translated, couldn't use a table....
basically I would like to leave column A where it is the whole time add the data to column B and C The use the data from column B to sort the values from column C numerically to column D (basically copy and rank then display the numbers in order). To throw in a twist if the data in column B is the same for two or more people the formula would need to take the lower number from column C to determine who gets the lower tag in column D.
having to create some kind of database in access....
View 1 Replies
View Related
Feb 1, 2013
The scenario is as follows:
Each row describes a physical object. Each object has several columns for various physical characteristics. Each object is in one of several numbered bins (1, 2, 3, ...). One column on the end combines the rest of the characteristics into one line {=B2&C2&D2...}
Here's what I would like to happen:
I would like one last column to be a "new bin number" for each object. If the combined characteristics column of a given object is unique in the list, I want "bin new" to not change. If the combined characteristics column of a given object is not unique, but the matching object has the same value for the original bin number as the object in question, then "bin new" again does not change. If, however, there are matching objects in other bins, I need "bin new" for that object to return the lowest "old bin" value of all of the matching objects. Consider the simple example:
mVBbUpP.png
Link: [URL] .....
From there, I can sort the list by the "new" bin column, and that would preserve the existing list, except matching objects would get bumped up to the earliest instance of that object occurring in the list. In essence, my final sorted list needs to keep the original bin groups together, except in the case of matches where the matched objects in higher bin groups get placed with their lower bin group counterparts. This "new bin" scheme is the clearest way that I see that can accomplish this.
My attempt at a solution:
xls file: binsort.xls
I thought of using arrays. For the example above, I would highlight G2 through G10 and input the following formula:
{=Min(if($E$2:$E$10=E2,$B$2:$B$10))}
and then hit ctrl+shift+enter. The issue is that the =E2 part does not iterate throughout, so it is always comparing the combined values in the 2nd row with itself, and thus returning "1" for every value in the "bin new" column. You can see this in the .xls file provided: column F is what I am trying to reproduce via the function, but column G is what I end up with instead.
View 9 Replies
View Related
Jan 13, 2008
how to sort based on the first two digit..
I need to sort the COMM CODE ( which is at Column no 3 )
View 14 Replies
View Related
Jan 20, 2009
I have a worksheet with columns of Unique Words and corresponding Frequency of Occurrence for several years. I would like to sort the data so the rows match up on same Unique Words across years.
I am using Excel 2007.
View 9 Replies
View Related
Jan 21, 2008
if there is anyway of sorting data from a word document into an excel spreadheet?
I want to sort be able to export the data according to the column on the far left hand side (month), into excel.
ideally i would have liked to have all the data in excel as it would make things easier but in this instance i need to keep all the data input in word.
View 9 Replies
View Related
Jun 29, 2006
I have two columns with the same data just totally different orders the third column (associated with the second) has data that I want to sort. I want to keep the order of the first, rearange the second so they match, and have the 3rd column follow the second to the proper location. i need to keep the order of column 1 so i can post into a massive spreadsheet. Theres gotta be a quick formula for this i just have no clue
View 2 Replies
View Related
Jun 18, 2008
I am using a vlookup and have a problem. I am assigning a category to an item number based on the first two characters of the item number. For example item number 60123 would equal scrap because of the first two characters of 60. But the item number can begin with either a number or letter. Here is the formula I am using that works for item numbers that begin with numbers:
=VLOOKUP(VALUE(LEFT(E2,2)),Sheet3!A:B,2,FALSE)
It works fine until I reach a item number that begins with a letter, then I get the dreaded #Value error. If I take the value out of the formula then it works for the letter based number items but not for the number based item numbers.
View 9 Replies
View Related
Oct 19, 2012
I am working on a new budget spreadsheet that I am creating. I want to accurately calculate credit card expenses. As everyone who has a credit card knows, charges made this month are not due to be paid until next month. To make matters more complex, charges made from (for example) January 20 until February 19 are not due to be paid until March 20. With that in mind, I created a spreadsheet with a different tab for each month. Each tab has a place to put in all expenses. Here is an example:
date
Category
Expenses
Remarks
[Code].....
Obviously, The above example is from January. IN the February tab, I want to calculate everything in the "Expense" column that has a date in the "Date" Column before Jan 20 and where it says "Kohls - Frank" in the Category column. Of course, in the March tab, I would need to grab all of the "Kohls - Frank" expenses after Jan 20 from the January tab, and everything before Feb 20 from the February tab.
I figured out how to add up everything with the following formula: =SUMIF(Jan!A:A,("<="&DATE(2013,1,20)&E32),Jan!C:C) How can I filter on the Category tab?
View 1 Replies
View Related
Jan 22, 2013
I have standardized each existing worksheets and any new worksheet to have certain parameters. The purpose of the Macro requested is ensure these worksheets are sorted alphabetically (and only these worksheets). The code I have been working with is contained below. It is working as expected but now I need to incorporate a statement which places the following condition:
Sort worksheets only if A3 has cell value of eCRF NameIgnore worksheet name: TOC & ENTER ECRF NAME
VB:
Sub Sort_Tabs()
'declare our variables
Dim i, j As Integer
[Code].....
View 5 Replies
View Related
Mar 1, 2014
I have found a list of newspaper in an excel file and at the end of the text it is written the name of the language e.g Bangladesh News 24 (Dhaka) [In Bangla], The Bangladesh Monitor [In English]. I would like to distinguish the names ended with [In Bangla] and in [In English]. How can i distinguish this two different type of data with VBA?
View 1 Replies
View Related
Jul 29, 2013
I am trying to do an analysis on 4 spreadsheets, each with approximately 4500 cells.
Each vertical cell represents a patient visit to a hospital. There is a timestamp, then a bunch of other metrics that I will be comparing. I want to sort the 4500 visits by time and date. This is how the timestamp of their arrival shows up:
Jul 02 2012 10:25 AM
^A single cell contains both the date and time next to each other.
I want to first separate all visits by time of day. I want to make one group that checked in between 10a and 10p and another for 10p-10a. Then I want to sort both groups in chronological order by date (i.e. starting with 2011 and ending in May 2013).
For one, I'm thrown off because the time and date are next to each other in the same cell.
View 4 Replies
View Related
Jul 31, 2014
I have a spreadsheet with column headers like, "Date" "Phone number" "Name" ect., and rows of data below. Each of these rows contains data pertaining to a prospect employee, as the purpose of the spreadsheet is to keep track of job applicants. One column is used as a 'Recruiter Sign-off' area, where the recruiter who processed the application inputs their name:
Example.png
I'm trying to automate a variation of excels sort function. However, I don't want to sort 'From A to Z.' Instead, I want to have the rows of data sorted with the 'Recruiter Sign-off' column used as the sort Key, and a selected name (of one of the recruiters) used as the sort Criteria.
I already have a userform that allows the user to select a name from a listbox. When they press confirm, the name is stored in a Public String variable, (selectedRecruiter). I'd like then like to have the rows below the column headers to be sorted based on the selected name. Example: User selects the name "Jon" from a list. The application then brings all of the applicants that 'Jon' has signed off on (those rows that contain his name) to the top rows of the range.
The workbook is shared, so I cannot use tables.
I found a code that accomplished what I want, however it also leads to a horrible system crash after a few times running it (BEX crash).
[Code] ..........
View 3 Replies
View Related
Mar 16, 2008
I have a sheet named: tool
In this sheet I have a long list of data that i would like to have sorted out on different worksheets that needs to be created by the macro. The deciding factor for the sorting are found colum B. In this colum there are different text strings. I would like for the macro to look at this text string and copy the entire line over to the respective work sheet. the work sheet needs to be named according to the text string in colom b.
Furthermore in the sheet named tool the first 3 lines are headings and i would like all of these transfered to each of the new sheets.
View 9 Replies
View Related
Jul 1, 2014
I have some cells that are summed with total cost.... now i need to sort the totaled cells? How do i sort a cell that has a formula based on multiple other cells?
View 2 Replies
View Related
Oct 17, 2006
Is it possible to sort the excel records based on background or font color?
View 2 Replies
View Related
