I am needing to sort individual rows horizonally (by columns) - sound simply enough, but apparently its not (unless I'm overlooking something really basic). For example, I need to sort each row (2 - 300) beginning in column B and ending in column P individually. I can do this - one at a time - using Excel's sort function, but its time consuming and this is something I need to be able to do on a regular basis.
View 2 RepliesI have been using this macro to separate lots of rows with a certain amount of blank rows.
Example:
Code:
Sub test()
Dim j As Long, r As Range
j = InputBox("type the number of rows to be insered")
Set r = Range("A2")
[Code] .......
I trying to change it so that, instead of inserting how ever many blank rows, it just copys the row above.
Example (Row A to C)
I have been messing around with macro recorder and i could select each row and paste it into the blank rows, but from researching on the internet selecting and pasting data seems to be a waste of resources (ram?) and i will be running this on a couple hundred rows (lots of data). Also, i would like to keep the functionality so that i can still choose how many times it copys/inserts each individual row from a input box.
I eventually want the data to end up like it is shown in the table but i am takin it one step at time because i want to understand what the code is doing.
I have some data that is 300 rows x 43 columns.
I'd like to sort the 43 colums by using some of the rows. Is this possible using normal excel?
Alternatively is there a simple way to sort the colums in VBA?
I'm trying to convert a very large list of phrases into one list of phrases in one column in excel.
The original list is separated by commas. I can get part of the list to show when I import they're coming up in hundreds of columns instead of one column with hundreds of rows. I want them to be listed horizontally, each phrase in a separate row but in one column. How do I do this? I would transpose from the columns into rows but it's cutting off the data when I import.
I'm trying to sort by City first, then by Report #, but keeping the highlighted rows together. If I use the custom sort it will through my D2's at the bottom and not keep them with their city, report, and D1. How do I get them to stay together? I have attached my document example.
View 4 Replies View RelatedI 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.
I am trying to sort/filter the information by columns but I need the information in each row to stay together for instance...
1 2 3 4 5
2 3 4 5 1
3 4 5 1 2
4 5 1 2 3
5 1 2 3 4
I need to sort the numbers in column 3 in ascending order or filter them but i need the entire row to move when sorted so it needs to look like this
4 5 1 2 3
5 1 2 3 4
1 2 3 4 5
2 3 4 5 1
3 4 5 1 2
I am using the list function to sort and filter the data in the columns but when i try to sort them in ascending order i get....
1 2 1 4 5
2 3 2 5 1
3 4 3 1 2
4 5 4 2 3
5 1 5 3 4
I don't know how to write formulas or VBA's or anything but am willing to try.
Is it possible to sort the data and get a result like the second set of data? and is there an easy way to make this fool proof so people that use the spreadsheet will not mess up the data?
I have hundreds of rows of data which I need to sort into headings in a new tab.
E.g Column D has about 40 names which occur many times. Column G has 4 digit numbers which are unique.
I want to create in a new tab a column for each of these 40 names, using that name as the heading and then list the 4 digit numbers unders that heading. So if the name name "John" appears 20 times in Column D, it will get a heading in the new sheet and there will be 20 unique numbers listed below it from the data in Column G.
I have been trying to sort an excel spreadsheet to no avail. I have read mutliple posts and searched online but I can not find a solution. This is what I have. I have three columns of data. column A is an account name. column b has some insurance coverage info and may have multiple rows that go with column A. Column C has the effective date of the coverages in column B. I want to sort column A in alphabetical order but when I have tried it does not keep column B and C together with column A.
Column A Column B Column C
company 1 General Liab 5/1/14-15
Workers Comp 2/1/13-14
company 2 workers comp 2/1/13-14
company 3 crime 5/5/14-15
D&O 5/5/14-15
Fee 4/15/14-15
I have a single spreadsheet with a few columns but hundreds of rows. Each row is a property reference (with other data such as address in the columns) with a type of charge in column J and the amount in column K. I'll try to replicate below ...
A B ....... J K
Prop1 Prop1Address Rent 100
Prop2 Prop2Address Rent 150
Prop3 Prop3Address Gas 70
Prop4 Prop4Address Water 60
Prop4 Prop4Address Rent 200
Prop4 Prop4Address Elec 80
Prop5 Prop5Address Service 90
I want the sheet to display a property on a single row so would look like as follows ...
A B ....... Rent Gas Water Elec Service Total
Prop1 Prop1Address 100 100
Prop2 Prop2Address 150 150
Prop3 Prop3Address 70 70
Prop4 Prop4Address 200 60 80 340
Prop5 Prop5Address 90 90
I realize that the easier resolve to this request is to use Access & we are moving to that application.
I have included a worksheet as an example.
We have a 'work in progress' (WIP) worksheet that contains 12 rows of data per job. Need to know how (or if) you can sort by the "Job #:" value while maintaining the group of information necessary for each individual job.
Not a normal sort because the values aren't entered in typical side-by-side row & column format.
I have created a spreadsheet which is tallying up various data. I have a number of different categories e.g. A, B, C, D and within each one of these are sub categories. I then have a tally for the number of each category and within that the number of each sub category. I then want to sort the data so that first the main category is sorted in order of highest tally to lowest. the sub categories however need to stay with their primary category. I then need to sort each sub category within the main category.
the data looks omething like this:
category tally sub cat number
A 6 A 3
B 4
C 6
B 9 A 1
B 6
I have a lot of carton dimensions that are always presented in the same manner and would like to be able to split the individual dimensions into seperate columns.
The format is always: Length Width Height. Presentation of data is always ???x???x??? or ??x??x??? (ie two or three chrs seperated by the letter X). If the full string is stored in column A I would like B to display Length, C the Width and D the Height. I find it easy to use LEFT for Length but struggling with Width and Height which I'm sure are easy, just can't get my head around it.
I have a worksheet which is populated from a macro using the following code.
Code:
Sheets("Create Sub Contractor").Range("B6:B65").Copy
With Sheets("Sub Contractor Information").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
.PasteSpecial (xlValues), Transpose:=True
End With
The problem is that some of what is being copied needs to be Proper and Some Upper, therefore I cant use a paste special option.
What I'd like to be able to do is format the columns in the destination sheet ("Sub Contractor Information") from row 4 down to what ever format they need to be individually as there are some columns that are numbers, some text and numbers........
I have created a spreadsheet to do all payroll calculations. Would like to print out each individual persons detail to give them a "receipt" of gross pay and deductions.
How I could take a sheet with 100 names and print out each line.
I have keycodes in a single column that represent various product attributes.
For instance:
1304DP_CVDA2_CC_
1304EP_CVD_CC_
1400BR_CV_O_
1610__RA_
Everything before the first underscore can be ignored, the keycodes are after the underscore. If there are two underscores (__) there are no keycodes for that product. The next underscore after the keycodes represents product categories and can be ignored. Is it possible to break individual keycodes into their own columns?
For example, 1304DP_CVDA2_CC_ would become:
1304DP_CVDA2_CC | C | V | D | A2
I have individual letter keycodes for the entire alphabet range A - Z, and one oddball A2.
I have a workbook populated with values in 3 columns. The values of the second column group rows into larger segments. I want to pull one row for each different value onto a different sheet.
example:
1. apple
2. apple
3. apple
4. apple
How do I only pull one row of apples onto another sheet without copying by hand? I have attached an live example. I want to pull one row for each classification (column 2) named on the sheet without copy/pasting each one.
i have a large table, columns A:F, would like a code which would look at the text in column F and create a new row for each bit of text separated by comma
probably easier to show what i mean, here is a section of the table, in this example store 13121 needs multiple rows.
start with this:
HTML Code:
table.tableizer-table { border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif font-size: 12px;} .tableizer-table td { padding: 4px; margin: 3px; border: 1px solid #ccc;}.tableizer-table th { background-color: #104E8B;
[code] ......
and turn it into this:
HTML Code:
table.tableizer-table { border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif font-size: 12px;} .tableizer-table td { padding: 4px; margin: 3px; border: 1px solid #ccc;}.tableizer-table th {
[Code] .....
My current project involves sorting a spreadsheet, selecting specific qualifying data from the sheet, storing it into a variable, the pasting the individual values from the list into individual cells on a different sheet within the same workbook. Here is the code I have so far...
Code:
Sub Test()
'' freeze screen updating to remain on main worksheet
Application.ScreenUpdating = False
'' move to Avaliable worksheet
[Code] ..
When this code runs the first item in the list pastes into Sheet10 A1, but no other values from the list are placed into Sheet10. Previous to this I had been using a variation of this code to push the list into a combobox list within a userform. In that case instead of
Code:
ThisWorkbook.Worksheets("Sheet10").Range("A1") = x
I had
Code:
Combobox1.list = x
And this worked perfectly; creating a list within the combobox (in fact I can use this modification to interrogate my code to determine if the list is being properly generated, and it is).
I have a Text ID Column and a Text Column. I need to save each row of the Text Column into its own individual text file on my C: Drive. The name of the file should be the the Text ID from the first column. I need this to work with different sample data, so the number of rows will vary. I also need this to be able to work with excel files that contain thousands of rows.
View 2 Replies View RelatedI have an Excel file containing data about employees. Each row contains a lot of information that is relevant for a specific date range, denoted by the columns "start date" and "end date". I would like to copy each row so that I have it accessible per date that is within the date range. The only columns that would change in the copy are the date columns. For example, for 1 row that has a date range of 01/08/2013 - 05/08/2013, I want to copy the row 5 times except the date columns become "01/08/2013", "02/08/2013", etc.
View 2 Replies View RelatedI need to insert blank rows at the end of the each data segment to add totals for each individual. Is there a macro that I can use for this? I'm attaching an example file.
View 8 Replies View RelatedIs there a code or some way for me to sort 4 columns together? I want to sort a list of employees and for each employee theres a column with their Lost Business, customer satisfaction, and two more columns. I want to sort all the columns at the same time so that the best employees overall will go to the top of the list and the worst ones will be at the bottom. Whenever I use the sorting feature it does each column independant of the others so everytime I sort a new column it just moves around the last one I sorted.
View 14 Replies View RelatedIm not entirely sure about this but it seems like if I have data in columns A - Z and sort in one of them, the data in Columns AA - AZ does not move accordingly rather it stays fixed.
View 4 Replies View RelatedI have a spreadsheet that is populated my Concatinating data from other worksheets. Some of the results are numbers, some is data while others are blank spaces and othersare set by the concactenating default of "" when the criteria is not met. I need to sort these colums. However it seems that the default "" from the formula is not a BLANK or a ZERO or an empty cell.
Those cells will place themselves at the top of the sort. I need to eliniate them. I have copied and pasted as values but that has no effect. Ironically I can identify the cells with an if statement so what I need help with is generating a macro that will cycle thru the range of results and delete the approriate cells.
I have tried the following:
lr = Range("A1").End(xlDown).Row ' Last Row
lc = Range("A1").End(xlToRight).Column ' Last Column
For Each cell In Range(Cells(1, 1), Cells(lc, lr))
cell.Select
AC = ActiveCell
If AC = "" Then Selection.Delete Shift:=xlUp
Next
But this does not work as it cycles top to bottom and leaves behind 1/2 the problem cells.
Lots of XLS files in a directory (C: emp). Each book has only a sheet1 with the data.
I have another workbook that i need to use to overwrite certain values in one column from each book.
The workbook I have has a column A and B, ID and value.
In each file inside the directory, A3 to last row used in A had each ID. If the id matches to an id in the workbook with the new values, I want to take the value from B in the override table and put it in C in the file. Look at each ID in each file against each ID in the override, replace and close.
I have an issue with some data that I need to sort into several columns. Basically, I have a column that has data listed in each cell like this; A 567 T 1 D3. What I want to be able to do is sort that data into several columns. I am not sure how to do this at all. I have attached my data to be looked at. I have already started the process but manually, and I don't feel like having to do this manually as this is very time consuming considering I am trying to sort the data into ~1927 rows and 5 columns.
View 2 Replies View RelatedI need help with either a formula or macro for sorting data into specific columns. I need the entries under the headings Ar,Bj... to be sorted into the correct columns. To add to the problem, the data may not be exactly the same as the heading.
Attached is an simple example of a spreadsheet where the top is the original and the bottom is what I need the final outcome to be. The data is pasted from a different spreadsheet and will be changing each time.
I have a potential of 5 columns of numerical data (simple number entries) which are entered manually in no particular order.
Is there any way of sorting the data so that it is presented in numerical order (smallest to largest) starting with the smallest figure at the start of column 1 up to the largest figure at the end of column 5.
I have three columns, one column is time every two seconds with data associated that time, and one is time every minute with an associated tidal height.
I want to sort my data so that for every 2 seconds I have an associated tidal height for that minute.
eg:
what I have:
Time Time Tidal Height
(2 sec intervals) (min) (meters)
1:15:00 1:15 1.342
1:15:02 1:16 1.221
1:15:04 1:17 1.115
...
1:15:58 1:43 0.024
1:15:60 1:44 0.012
1:16:00 1:45 0.008 ....................