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 RepliesI 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 RelatedI 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.
I have a database of names in one column in excel 2010 and over time I have coloured the text of some of the names green to show that they have been added to a new online database. I want to be able to count how many of the names have been coloured so I can see if it matches with the total number already added on to the online database. I have over 800 entries so I don't want to have to manually count them unless I find I have missed any. Conditional Formatting is greyed out (it is a Shared document so that might be why).
in creating an IF (text is a certain colour) THEN 1, 0 or any others ways I can find the total in that colour. I only have two text colours in my spread sheet, black and light green.
Example of Table.JPG
I have a list of 2,000 plus numbers and the list contains numbers and text. When I sort or filter, the only option is sort A to Z (or Z to A), but the result is all of the numbers are sorted, then the numbers with letters are sorted. How can I sort both? I attached a excel example of what I am looking for.
View 4 Replies View RelatedWhat I have is a bunch of text files and 2 spreadsheets that sort the text files. I’m not sure but I think there might be a better way but the problem is I don’t know VB all that well. Can someone take some time and look over this stuff and let me know if there is actually a better way to make this happen. I also noticed that there are 2 txt file missing and not added in the sorted file (PS3_HIGH, PS3_URGENT)
The text files come from another network and are transferred manually every day to a folder. The purpose of the .xls files is so that someone can look at the counts and try to lower the numbers from day to day. If something is alarming frequently, the current files do not put that information together well. The numbers are still important, but is there a better way to do this sort in excel?
I have numbers like this and their sorting wrong. I want to sort in decending order by last digit to the right.
How it's sorting now: (sorting by first number sequence)
12/31/05a
12/31/05
11/01/06
10/31/06
I would like it to sort like this:
11/01/06
10/31/06
12/31/05a
12/31/05
These WERE text numbers that were formulated to read like dates. Is there any way to munipulate how to sort? I'm just looking to sort it like it was a date. I get text files in formats like this:
09062006 and I formulated them to read 09/06/06 like it would be a date.
sort the list below:
40105
40106
40107
40105A
40105B
40105C
40105D
40106A
40106B
40106C
and the list will look like this:
4010
4010A
4010B
40105
40105A
40105B
40105C
40105D
40106
A B
1 List 2 things that went well during the meeting
2 Election of Chair Election of Vice Chair
3 Powerpoint Presentation
4 Presentations were informative Everyone at the meeting took part in Q&A
5
6 Group discussed Gardens Upkeep We were mostly in agreement
7
For each meeting attendee (row) they will give their answer to the question (A1).
Some will write no answer, some 1 answer, some 2 answers.
I need to list these answers in another sheet on seperate rows, thus seperate cells, in one column. Without having a gap between each of the answers on that sheet.
I have been wracking my brains and came upon this site searching via that best loved search engine we all know.
I would be very greatful if anyone could help me here.
I'll be watching this closely, nothing else to do.
so I'll be giving quick replies.
I have a column of over 1000 cells with context in it. Each cell as a keyword in parathesis associated with the context. Therefore, different context are associated with the same keyword. However, the cells with the same keywords are spread out all over the 1000 cells.
Example:
cell1: anti-lock brake system (abs)
cell2: baromic pressure (Baro)
cell3: anti brake systematic (abs)
...
Basic solution:
I need to create a vba code that would look through the 1000 cells for a specific keyword and copy all the cells having that keyword in parathesis into another spreadsheet or column, so I can look through them easily. so if i look for (abs) it will give me:
cell1: anti-lock brake system (abs)
cell3: anti brake systematic (abs)
...
Ultimate solution:
The VBA code would be "smart" enough to copy all 1000 cells in keywords order. It would give me all possibilities:
cell1: anti-lock brake system (abs)
cell3: anti brake systematic (abs)
cell2: baromic pressure(Baro)
...
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 RelatedI 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]....
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 RelatedA column holds numbers and numbers with suffixes. The conventional sort function pushes the text values to the bottom. I need them sorted along with the numbers.
Microsoft's KB says to add a column formatted as text and then to RETYPE ALL OF THE VALUES! Splendid. But my file has hundreds of records. Re-typing all of them would be a major pain. On the other hand, if I had started typing while trying to find a way to sort the darn thing I might be done by now.
Here's what I know so far: If we create a column, format it as text and then populate with 1, 2, 3, 4, 1a, 2a, 3a, 4a, etc. it will sort exactly correct (1, 1a, 2, 2a...) after selecting the "sort numbers and numbers stored as text separately" when the Sort Warning appears. If you try to create the sample I typed here, you need to be careful that the area is formatted as text BEFORE entering the text. You apparently cannot change the format of an existing column of data to Text or if you do it does not have the desired effect when sorting. It might be useful to know if this can be done too. MS suggests not.
I have a series of identification values that begin with text and end with a number. The trouble with sorting on this field only affects where the the first 9 entries end up since the number portion is not prefixed with a zero. Is there a way to sort these numbers so that ID2 (and not ID10) follows ID1?
ID1
ID2
ID3
.
.
.
ID9
ID10
ID11
Lets say I have in column A a series of cells populated with words or phrases delimited by ;; and I want them sorted within the cell. I.e.
This:
Word ;; Another word ;; This is a phrase
Should become
Another word ;; This is a phrase ;; Word
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....
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.
how to sort based on the first two digit..
I need to sort the COMM CODE ( which is at Column no 3 )
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.
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.
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
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 RelatedI'm currently trying to make a sort macro that sorts a row of data that has a combination of numerical data and the text entry "N.A."
e.g.:
My field:
1
2
N.A
4
5
N.A
9
I want to sort the cells by descending value. But the result appears as:
My field:
N.A
N.A
9
5
4
2
1
But I want to numbers to appear first and have the "N.A."s to be after the number, since I have about 100 columns and some rows have like 30 N.A., which is pretty bad for usability if all that appears in the first screen are "N.A."
EDIT: by the way by ascending the data is fine since it orders by smallest number to largest and THEN the "N.A."
I need to custom sort a column. I have 3 different types of data in the column. First - multiple dates, Second - "TBA", and Third - "ASAP". What I need is when the column is sorted the "ASAP" rows will be first, the dates (sorted) will be next and finally the "TBA"s. I have been trying to use a custom list.
View 2 Replies View RelatedI have a list of about 300 addresses that I'd like to be able to put in a sortable state. In column A1-A4 is the following:
Name
Street Address
City, State Zip
Email Address
Then there is a blank row and then the next address. What I would like to do is figure out a way to take the info in columns A1-A4 and move them horizontally to A1, B1, C1, D1, respectively. Sure, I could cut and paste, but it would take me forever. I tried a macro,
I have 5 columns. The first 4 columns only contain 1 line each, the last column can contain anywhere from 1-6 lines. How can I do a sort and keep all the text in the last column together? By way, it was imputed on separate lines.
View 9 Replies View RelatedI 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?
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].....
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.