Merging 2 Columns Into 1 Alternating Every Other Column

Aug 12, 2010

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!

View 5 Replies


ADVERTISEMENT

Count Groups Of Alternating Rows Across 3 Columns?

Jan 18, 2014

I'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

View 4 Replies View Related

Select Alternating Columns With Unknown End Point

Feb 18, 2010

so 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.

View 7 Replies View Related

Separate A Line Of Alternating Text/Numbers Into Columns

Jul 20, 2009

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?

View 9 Replies View Related

Count Alternating Values In Column?

Aug 25, 2014

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

View 4 Replies View Related

Sort Column In Alternating Manner

Mar 13, 2014

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?

View 1 Replies View Related

Merging Two Columns

Dec 20, 2006

i would like to merge two columns as explained below

for ex

the 1st column is:
1
2
3
4
5

the 2ed column is:
1
2
3
4
5

the merged columns should be:
1
1
2
2
3
3
4
4
5
5

View 9 Replies View Related

Combining/merging Columns

Mar 18, 2009

ive got columns of dates that i want to aggregate up into a single column of distinct dates. the dates columns are of varying length, some with gaps in the middle of the column, and some columns are blank. dates in teh columns may duplicate, but i only want distinct dates copied in the single column.

i cant do this by hand because there are dates in around 200 columns so i need a macro or way to do this quickly. ive attached a picture to explain the problem.

View 5 Replies View Related

Merging Two Columns Of Information

Jul 16, 2009

I have two very long columns of client names. The columns represent reports that were run at different times on the same information.

What I want to do is merge the two clomuns. The issue is that there are many names that are the same but some that are different. Is there a way that you can merge the info. So it ignores the client names that are the same but auto adds the ones that are new. There are financials in other columns that I want to have come along during the merger.

View 7 Replies View Related

Merging Two Columns Into One But Having It Separated By Hyphen?

Jul 8, 2014

i have two columns with account numbers, i need one column with both account numbers, separated by a hyphen, see below the first line would be 10-15.

10 15
10 20
10 30
10 40
10 41
10 41

View 3 Replies View Related

Merging One Columns Data To The Bottom Of Another

Jan 24, 2009

Give me the code to attached one column of data to the bottom of another?
ie the next available cell

so column a has 250 lines of data the data from another column will then go diectly into 251

thats not the number i want but the next available

View 13 Replies View Related

Merging Two Columns And Keeping The Data From Both

Jul 26, 2006

There is one crucial feature to the 2007 Excel that has been overlooked.

Throughout all versions of Excel there has always been the feature of
converting text to table, however there is no way to do this in reverse.

There is no way to merge two columns of data and to keep all of the data
without one column overwriting the other. If only there were an automatic
way to merge two columns of data and to be able to place a delimited
character in-between, just like the “Convert Text to Columns Wizard”, except
in reverse. Currently, the only way to merge two columns of data is to
manually go row-by-row and cut and paste them together. However, for 500,000
rows of data… this is impossible. Or to use a function to merge two columns,
however this requires that the original two columns remain. This is also
unacceptable. If Microsoft really wants to make Excel more functional, how
can this vast improvement be overlooked?

View 10 Replies View Related

Merging Columns And Rows Of Data Into A Matrix

Aug 3, 2014

I've been trying unsuccessfully to find a way to merge data into a matrix, but can't quite figure out a way to manage it. I have a list of employees in column A, in the list there are employees, many with multiple instances, and in columns C onwards there are dates in dd/mm/yyyy format.

Each line represents the number of nights in a trip, from 1 to 35 nights and the start to finish dates. Where as some employees may only have one row with one date others may have 20 rows with between 2 to 35 dates.

I'd like to merge this into a matrix with one line for each employee showing which nights in the year they were away (tab matrix) which has a day of the year to illustrate which employees were away on which nights of the year.

I've uploaded an example to highlight what I mean.

I've tried a pivot and an index match formula but couldn't get either to work.

View 7 Replies View Related

Excel 2007 :: Merging Data Within Two Columns Into One

Dec 5, 2013

I have two columns

Req | Expiry Date
yes 11/12/2010
yes 08/06/2012
yes 02/07/2017

how do i merge both columns into one.. not losing any data hence

Req Expiry Date
yes 11/12/2010
yes 08/06/2017

I am using excel 2007 ITS FOR WORK!

View 9 Replies View Related

Moving Data From Single To Multiple Columns And Merging?

Dec 16, 2012

Current Data:
File 1:

Each set of data is listed in either two or three rows

Eg.,
ID
Date
Filename

ID
Date

ID
Date
Filename

The goal is to move them to separate columns (rows can be 3 or 2 for each data set, and may or may not be separated by space/additional row)

File 2:

Has a common field 'ID' as that of File1, does not have Date, and Filename, but has a new field 'Detail' (already in the expected format)

Eg.

ID Detail

The goal is to merge properly formatted data from File 1 to File 2

Eg

ID Date Filename Detail

View 14 Replies View Related

Merging Columns / Deleting Rows And Summing Totals

Mar 22, 2012

My data in excel looks like this:

A B C D
Visit_Date Day_of_the_VisitHousing_Tour_TimeNumber_in_Party
3/22/2012 0:00Thursday 12:00 P.M. 2
3/22/2012 0:00Thursday 12:00 P.M. 4
3/22/2012 0:00Thursday 12:00 P.M. 1
3/22/2012 0:00Thursday Not Requested 2
3/22/2012 0:00Thursday 12:00 P.M. 3
3/22/2012 0:00Thursday 12:00 P.M. 3
3/22/2012 0:00Thursday 12:00 P.M. 4
3/22/2012 0:00Thursday Not Requested 1
3/22/2012 0:00Thursday 12:00 P.M. 2
3/23/2012 0:00Friday Not Requested 2
3/23/2012 0:00Friday Not Requested 5

What I'd like to be able to do is if Column A,B, and C are the same to sum column D. So, for example, I would like the data above to look like this:

A B C D
Visit_Date Day_of_the_VisitHousing_Tour_TimeNumber_in_Party
3/22/2012 0:00Thursday 12:00 P.M. 7
3/22/2012 0:00Thursday Not Requested 3
3/22/2012 0:00Thursday 12:00 P.M. 12
3/23/2012 0:00Friday Not Requested 7

View 2 Replies View Related

Merging Code For Hiding Columns And Setting Chart Axes Values

Feb 23, 2010

I'm trying to create a code that when run hides a selection of columns and defines the minimum and interval value for a chart on the active worksheet. My attempt is assigned to the 'update skills' button at the top of the "GRAPH" worksheet of the attached workbook.

View 2 Replies View Related

Combining Unique Data Into One Row With Columns While Merging / Deleting Duplicate Information

Jun 13, 2014

I have data in excel that has some information the same with a unique field. See below:

Last Name
First Name
ID
Date
Address
Apt #
Acct #
Code 1

[code]...

I want it to consolidate all of the like information but add on the codes in separate columns like this:

Last Name
First Name
ID
Date
Address
Apt #
Acct #
Code 1
Code 2
Code 3
Code 4

[code]...

Also, sometimes the same code is used multiple times but i want any duplicate codes to show as separate codes.

View 5 Replies View Related

Merging Data From One Column To One Cell?

May 3, 2012

What would be the formula to merge serveral rows of one column to one cell and separate by a ":"

View 7 Replies View Related

Merging Blank Cell In Column

Aug 2, 2012

I am have data in as shown below where there a number and few blank cell. I would to write a macro that loops through the column and merge blank cell with cell nonblank cell above.

20085

blank

blank

20086

blank

blank

blank

blank

blank

20087

blank

20088

blank

blank

20089

View 2 Replies View Related

Merging Cell Data Into One Column

Apr 30, 2007

i have received a file with thosands of rows of customers in it and their are two surname columns, i need to merge these two columns data together into one column.

View 9 Replies View Related

Merging Data Into A Single Column

Apr 2, 2008

I have a very large spreadsheet, 25 columns and over 16000 rows. I want to merge all of the values into a single column. One caveat, not every cell has data in it, as shown below:

C1 C2 C3 C4....C25
2

View 9 Replies View Related

Make A Column To Use As A Filter For Mail Merging

Jun 14, 2009

I'm trying to make a column to use as a filter for mail merging. The filter is based on a result entered into one column which is compared with a range of values in an adjacent column. I managed to get the nested IF formula to work but only when entered into a blank spread sheet or if I correct the error as prompted. I've attached an example of the spreadsheet.

View 2 Replies View Related

Merging Lists From Two Sheets Into Single Column

Feb 28, 2014

In Sheet 1, I have a list starting in row L1.

Col L
John Doe
New York
Past Locations
Previous Jobs
Male
Previous Schools

In Sheet 2, I have another list where the user will manually enter sub elements for some of the elements of Sheet1. It looks like this...

Column A |Column B |Column C
Past Locations|Previous Jobs|Previous Schools

Below that last row of headers, the user will make inputs. the list for each column should be able to run down to say 100. The inputs may look like this....

AZ |Plumber |XYZ Elementary
MD |Waiter |ABC High School
IL
CA

Now, the problem is I need to combine these two into a single column in Sheet 3 in a special way. The output would look like this.

John Doe
New York
Past Locations
AZ
MD

[Code] .....

Notice that I am taking the list in Sheet 1 as is, until i run into a value that is the header in Sheet 2, in which case I take all the contents of that header column until I am done, and then go back to sheet one, and repeat the process. I need the output in one column in Sheet 3; i don't need any special formatting.

View 3 Replies View Related

Merging Multiple Rows In Column Without Losing Text?

Jul 3, 2013

sample merging file.xlsx

I have a data array of 7 col and 256 rows. I want to focus only on the first 2 columns though. Here is what is going on. In the first column I have numbers from 1-256, these numbers are merged to the appropriate size to accommodate the data in column 2. However, the data in column 2 is not merged, it consists of 1,2, or 3 lines of text. Excel deletes the text in the rows below 1 if I try to merge, excel does not allow multiple row merge selections. What I am looking for is a formula that looks at column 1, finds how many rows are being merged in column 1, then looks at column 2 and identifies the number of rows needed for merging and displays that text in only one row. Basically, it's a formula that merges multiple rows of text in col 2 depending on the number of merged rows in col 1.

No.Customer/Operator
1Fairbanks Morse /
Beloit, WI
2H+H Umwelttechnik /

[Code]....

View 1 Replies View Related

Alternating Odd And Even Numbers?

Mar 15, 2014

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

View 4 Replies View Related

Excel 2010 :: Move Part Columns Of Data From 6 Columns To Form 1 Large Column In Column A?

Jan 31, 2013

I have a large spreadsheet converted from pdf whose data still appears in A4 reading format.

I need to move part columns of data from 6 columns to form 1 large column in column A.

For example, move range B8 to B76 beneath range A8 to A76 and range C8 to C76 beneath that etc, page by page working through all 270 pages !

Also need to delete unnecessary 'page headers' throughout as in rows 2-6

View 2 Replies View Related

Calculating Alternating Values

Mar 6, 2009

I have a bunch of start End times in columns calculating total time :-

View 12 Replies View Related

Alternating Range Formatting

Sep 13, 2006

I'm writing a macro thats 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

View 8 Replies View Related

Color Alternating Rows

Nov 29, 2006

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 Related







Copyrights 2005-15 www.BigResource.com, All rights reserved