I have sets of data... like so:
A word text blablabla
B word text blablabla
A dog cat france
B dog cat france
B this that other
A this that other
A tiger dragon monkey
B tiger dragon monkey
My source data I can't get to sort alternating on columns A and B, and I would like the data to alternate A-B-A-B-A-B while retaining the pairings of data in the adjacent fields. Is this possible?
I have a 1640 line database in Excel to sort in a specific manner and create a booklet in Publisher for a Family History group.So here is the data as it appears at the moment;
SURNAME - FIRST NAME - DATE 1 - F SURNAME - F NAME - PARISH
DATE 2
Date 3
SURNAME - FIRST NAME - DATE 1 - F SURNAME - F NAME - PARISH
DATE 2
DATE 3
So A to Z sort Column A BUT retain DATE 1-3 in the finished state,and same for next 400 groups.
I'm sure there's a simple way to do this (I've been trying countif/match formulas but I'm not quite there)! I need to group line items with the same dollar value, WITHIN say 1 cent. If I do a pivot it will group exact matches, but I'm looking for the flexibility to group something within a few cents. So instead of pivots, I was trying to think of a formula that would do what Column A is doing below:
*I manually added Column A, looking for a formula that would group dollar values within 1 cent, assuming I've sorted Column B from smallest to largest:
Column A : Column B
1 : $2.13
1 : $2.14
2 : $3.00
2 : $3.01
2 : $3.01
3 : $4.00
I'm looking for a way to put data into 1 column from 2 different columns, alternating every other row.
The data looks like this:
A B C
1 2
3 4
5 6
And I want C to look like this:
C
1
2
3
4
5
6
I don't necessarily need to keep the data in columns A and B, as long as C follows this format.
Excellent, just what I was looking for. I couldn't get the UDF working right, I'll try messing around with it, but the Index solution works great!
Assuming i copied a list of cells that are vertical [column format], how do i paste it such that it will become horizontal [horizontal format]
View 11 Replies View RelatedI create a report where in I need to rearrange the columns in a particular manner as required by the client. Any macro which will will work as per following criteria.
Column headers start from A1 onwards (the last column header might vary depending on the creator, I get the report from a different team). The number of rows of data might vary.
For changing the order of columns, the macro should look for a column with the header "Close Date", cut it and insert is before a column with header "Bookings". Then, the macro should look for a column with the header "Description", cut it and insert is before a column with header "Selling BU". The macro needs to then cut two adjacent columns namely "Sector" and "Sub-Area" and insert it before the column with header "Total Bookings".
I have searched numerous times for the answer I need, but no joy yet.
I have a spread sheet that has one column (call it 1) of data with a single entry each of 100, 200, 300, 400, 500, and so on.
I have a second column (call it 2) that has multiple entries of 100, 200, 300, 400, 500, and so on, and I want to sort so that the rows wind up with the numbers in column 1 all sorted in an accending order, but I want all the same numbers in column 2 sorted to "list" next to the same number before the next number sorts. See below for a small example of what I hope to see once it sorts.
100
100
100
100
200
200
200
200
300
300
300
300
Auto Merged Post Until 24 Hrs Passes;Unfortunately the example didn't post correctly..perhaps this will work but note that the x's are in column 1 and don't exist.
100
xxx100
xxx100
xxx100
200
xxx200
xxx200
xxx200
300
xxx300
xxx300
xxx300
Using DataEntry sheet for data.
Trying to rearrange the data to DataFormatedProperly sheet.
So far all I can accomplish is DataFormatedWrong sheet.
Edit: Not sure what happened but file was NOT understandable before. It should be correct now.
I have a column (k) of 65,000 rows with numbers that are either positive or negative digits. The next column (L) has classified them as either 1 for positive number or 2 for negative.
There may be a run of all positive or all negative numbers or 2 positives and 3 negatives or any combination of positive and negative numbers reading down the column.
Reading down the column (from L1), I need to find any sequence of alternating 1 positive, followed by 1 negative or vice versa.
I am looking for evidence to find any sequence that has more than 2 alternations. (Remembering that positive is 1 and neg is 2)
That is, two or more alternations of a positive followed by a negative or neg followed by a positive.
eg
1
2
1
2
or
2
1
2
1
I have a bunch of start End times in columns calculating total time :-
View 12 Replies View RelatedI'm writing a macro that’s taking user input (from a form I developed) and will generate a chart according to the input.
One requirement for this chart is that the background alternates fill colors between gray and white. Every four columns need to be either gray or white. Based on the duration the user entered, the width of this chart is created. The length (the number of rows) is constant.
I've tried different loops (for, if, do - you name it I tried) This loop is just out of my league. Any and all help would be greatly appreciated.
Summary of Facts:
- Starting Point of Chart is B2 (the top left corner of the chart will be in B2)
- the number of columns is dynamic (based on user input)
- The rows are constant are (2 through 16)
- Every four columns need the fill color to be either gray or white
to tint/color alternating rows with white on my worksheet so when new rows are entered the sheet will adjust maintaining this pattern
View 6 Replies View RelatedI was wondering, what would the macro be to colour alternating rows.
So I could select the range of rows, hit the macro - and it would colour them for me?
I need it to do alternating green / lightgreen. For one, but I need to do other colours afterwards as well. So what would the code be to do this?
I have a spreadsheet that contains thousands of rows and I need to highlight all the duplicates in Column B.
What I need though is one group o duplicates to be colored one color and then the next group of duplicates be colored a different color. I need just a 2 color banding.
My need for 2 colors is that the cells contain 9 digit numbers and there can be duplicate groups right next to each other and for the eye to distinguish a different grouping of cells I need that 2 color banding.
I have the below code that colors everything in one color. Can I be modified or a completely new code to do 2 colors?
Code:
Sub Dups()
Dim Rng As Range
Dim CL As Range
Set Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
For Each CL In Rng
If WorksheetFunction.CountIf(Rng, CL.Value) > 1 Then
CL.Interior.ColorIndex = 6
End If
Next CL
End Sub
My spreadsheet has rows with inspection numbers in column A, but there are varying numbers of rows associated with an inspection number. I'd like to change the color of the rows for every other inspection number to yellow using a formula. I think it has to involve an if/then statement but I can't figure out how to word the formula to just choose every other group of inspection numbers.
Example:
inspection numbers
date
description
[Code].....
Is there a way to shade alternating rows in a range and have it always be alternating no matter if a row is added in the middle of the range. If I do it manually now and add a row it messes up the shading and fixing it manually is very time consuming.
View 7 Replies View RelatedIn column H I have dates from 1/1/2008 to 12/31/2013. What I would like to do is transfer that data to column J, but alternate it every other row and then insert the word Date into the blank cells. For instance J1 would read "Date" and then J2 would read 1/1/2008 J3 would be Date and J4 would be 1/2/2008. I have a large database and I tried to use array formulas and they slow it down to a crawl so I was working with DSUM, but I don't feel like wasting time with adding in Date manually.
View 8 Replies View RelatedI'm looking for a way to count groups of alternating rows of "TRUE" values across 3 columns. There will never be an occurrence of more than one "TRUE" value per row. In the sample below, alternating TRUE values occur in rows 4 through 7, so this would be counted with the groups of 4.
1Column 1Column 2Column 3
2FALSEFALSETRUE
3FALSEFALSETRUE
4FALSE TRUEFALSE
5 TRUEFALSEFALSE
6FALSE TRUEFALSE
7 TRUEFALSEFALSE
8FALSEFALSETRUE
9FALSEFALSETRUE
10FALSEFALSETRUE
how to add alternating cells when all are numbers, e.g. B8 to KW8 are all numbers and I want to add B8, D8, F8, etc. until KV8 and then separately add C8, E8, G8, etc until KW8. Is there a more efficient way to do this than =sum(b8,D8, etc)? I also need to do the same with =counta(b8, d8, etc).
View 2 Replies View Relatedso I have been googling to figure out how to write a macro that will select every other column on my workbook. All that keeps coming up is conditional formatting, which I do not want. I am not trying to highlight the columns in alternating colors, I just want to select them.
I am starting at column F and I would like it to search until the end of the columns and select every other column ending with the last column with data in it.
I know I could search by a specific row and tell it to find the first blank cell then offset it, but there is no particular row that will always have data in it, so I guess it needs to check by column until it finds the first column without any data in all rows and then offset it.
Anyone know what I am talking about here?
I know it should be fairly simple but I couldn't seem to find any answers anywhere else.
Also, anyone know any good websites that have definitions for some basic macros? I'd love to start learning how to write this stuff on my own.
I am interested in selecting cells in alternating rows as follows:
12 Red Happy
00 Unique descriptor
13 Grn Sad
03 Unique descriptor
14 Yel Happy
02 Unique descriptor
I am interested in selecting all the "12, 13, etc", "Red, Grn, ..." and "Happy, Sad..." for analysis, but want to skip all the rows containing the 0- numbers and the descriptors for them. How do I quickly select only the alternating cells in a column within a selection?
Here's what I have.. (on a much smaller scale)
http://www.jmetenterprises.com/produ...pics/excel.jpg
(notice how the lines that match are now even.)
[Edited by admin~ *Link* to large images, don't display them]
I am having a hard time getting my match functions to work with letters and numbers in the same range. I am also trying to figure out a way to have the ranges switch if there is an error. because the data is in multiple columns. It is hard to explain so I have attached a sample workbook.
ExampleFile.xlsm
I'm trying to use the following formula in conditional formatting in cell A2 when the range A2:AW64 is selected (this conditional
formatting will be copied to all cells in the selected range):
Formula is: =isodd($A2)
The error I keep getting is this:
"You may not use references to other worksheets or other workbooks for Conditional Formatting criteria."
I'm trying to use this conditional formatting because column A is our "Order" column. "Order" can be from 1 to 13 and this column
is sorted ascending. There can be multiple rows in each Order number. So I want to shade all 1's, 3's, 5's, etc... so that each
Order group has alternating shading.
I also want to use conditional formatting, not VBA, to get this to work. In the future, rows might be added to an Order number or two and I want the shading to automatically adjust.
I have thousands of lines of data that are of the following form:
Jackson, TN 9623 BCBS TN 98 UnitedHlthCare 2
Jacksonville, FL 3577 Aetna 47 BCBS FL 37
Knoxville, TN 3796 Cariten 44 John Deere 41
Lakeland-Winter Haven, FL 2165 UnitedHlthCare 31 Aetna 29
I need to separate these lines into columns such that, for example, the first line would be in 6 columns: (Jackson, TN) (9623) (BCBS TN) (98) (UnitedHlthCare) (2)
The delimiter does not work because some of there is no one character that always separates the text and the numbers. I've tried going through in word and typing something like '%' where I want to separate, but with thousands of lines of data that is extremely tedious.
Is there any formula I can use that would be capable of solving this?
is it possible to leave a column out of this sort code as in A the very first column which just numbers the rows?
Here is my sort ....
I'm looking for a way to sort dates from several columns into a new single column (perhaps multiple columns if the entry columns become too numerous). I've included an example. There are currently only 4 columns, but there may be as many as 20 in the future, each with 20 dates under each heading. Any blank cells would be eliminated. If I filled a blank with a new date, that date would be placed into the chronological column. So basically, this would take the date from several different categories and create a single calendar of events.
View 8 Replies View RelatedI believe that the best way to evaluate this request is to look at the example. I have 2 buttons to demonstrate what I need, along with written instructions.
eMailSampleTest.xls
i want to sort column D acsending, then column C descending. i want to do this using VBA because i cant just format the sheet because another vba macro paste the sheet thier which would rid the formatting. (i would do it myself everytime but it is for someone to make it easier for them and not make mistakes..i searched, just found a sum formula post)
View 3 Replies View RelatedI am looking for a way to highlight groups of identical number cells in alternating grey and white. My goal is to make it clear when there is a repeating set of numbers. Below is an example of what a completed state of this would look like that I created manually. The real form I will be using this code on will have long number which is why I am looking for this added clarification. I am using excel 2010.
1
1
2
3
4
[code].....