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


ADVERTISEMENT

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 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 4 Rows Of Data Into 1 Row

Jun 27, 2014

finish up a macro. The first part of the macro that is already working copies and paste 4 cells content from each worksheet found in a closed workbook thus generating 4 rows of data for each worksheet.

How to merge those 4 rows of data into one row. I have attached an example with the original data and the results data I am looking for but here is an explanation:

The column that is a constant and that I want to match is column B (Worksheet name). The 4 rows generated per worksheet will be showing on the first row data in Column C and columns D,E,F will be blank; then the 2nd row of data will have column C blank, column D with data and column E,F, and so on for the next 2 rows. What I would like the macro to do is merge all 4 rows so that the data found in cell C,D,E,F can be found in one row.

example.xlsx

View 9 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

Merging Multiple Rows Of Data

Mar 20, 2008

I have several worksheets in a single workbook.

I have the following

employee paymethod earningcode earningtype rate hours amount
jane doe H CR R 5.00 80 400.00
jane doe H CO O 7.50 2 15.00
jane doe H HL R 5.00 8 40.00
jane doe H SE R 10.00 10 100.00
john smith H CR R 6.00 10 60.00
...

I need to have

jane doe CR 5.00 80 CO 7.5 15.00 HL 5.00 8 SE 10.00 100.00

I will have multiple employees. Not all employees will have all 4 earning codes (some will only have one, some will have two, and some will have three).

View 9 Replies View Related

Sorting And Merging Data From Two Rows

Apr 26, 2007

I have two rows of data that I need in one row and then sorted, it is possible that the data will be difffering lengths depending on input conditions. I also need the option to sort it either ascending or descending. The data in the two separate rows depend on other parts of the system and in turn this data will be used in another part. So I need it sorted automatically. I have tried ranking the data and then sorting it by rank but for some reason this does not appear to be working.

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 Duplicate Rows With Unique Data?

Jun 5, 2013

I have attached a before and after image of what I am looking to accomplish.

In the before image, you can see that there are 3 rows of data
- a header
- a repeating model number (in column F) with accompanying data (values in columns G - J are the items of interest)

In the after image, you can see that I took the 3 rows of data and turned it into simply two rows of data
- a header
- the repeating model number

What I did however in the after image, as can be seen, is I took certain data values that appeared on the repeating row (columns G - J), and included them as PRICE, COST, BEGIN, and END values in new columns on row 2.

Is there a way to accomplish this via some functions or maybe even a VBA script?

View 3 Replies View Related

Merging Duplicate Rows And Keeping Original Data

Feb 18, 2014

I need to merge rows with duplicate values in column A (Patient Name being the most important one), with columns B, C, & D usually having different isolated values as well. Columns E, F, G, H, & I are date columns, but the data is always going to be the number 1, meaning a patient was seen once that day (if they were seen two times that day for different reasons, information would be in an unmerged second row [same patient name listed in two separate rows], where columns B & C would be different). Column J is an autosum of columns E through I if that makes a difference. Column K is a notes column. The data that needs to be merged is always added to the bottom of the spreadsheet in order to show that a patient was seen on any given day, with columns B through K almost always being blank. Example:

Column A---------Column B---Col C----Col D--Col E--Col F--Col G--Col H--Col I--Col J---------Col K
Patient Name-----Therapist---Shared--%P----2/3----2/4----2/5-----2/6----2/7---Total Visits--Notes

Alice Alpha--------AB----------PT-------1---------------------------------------------0-------------blah
Boris Beta---------BC----------SELF----2----------------------------------------------0------------blahblah
Carl Carlisle-------CD---------PTA------3----------------------------------------------0
Carl Carlisle-------AB---------SELF-----2----------------------------------------------0
Donny Delta-------DE---------PT--------1---------------------------------------------0
Ernie Elephant-----EF---------PTA-------2---------------------------------------------0
Alice Alpha-----------------------------------------------1
Carl Carlisle--------------------------------------1--------------1---------------1
Ernie Elephant-------------------------------------------1---------------1

This is what I'm hoping it can look like:

Column A---------Column B---Col C----Col D--Col E--Col F--Col G--Col H--Col I--Col J---------Col K
Patient Name-----Therapist---Shared--%P----2/3----2/4----2/5-----2/6----2/7---Total Visits--Notes

Alice Alpha--------AB----------PT-------1---------------1-----------------------------1-------------blah
Boris Beta---------BC----------SELF----2----------------------------------------------0------------blahblah
Carl Carlisle-------CD---------PTA------3-------1--------------1---------------1------3
Carl Carlisle-------AB---------SELF-----2----------------------------------------------0
Donny Delta-------DE---------PT--------1---------------------------------------------0
Ernie Elephant-----EF---------PTA-------2--------------1---------------1-------------2

In this example Carl Carlisle is being seen for two different things, however how would it be written so the macro would know which Carl Carlisle row to merge with? I'm thinking that before running the macro I could manually enter the information into column B so it knows which Carl Carlisle row above to merge with.

Data always starts at row 14 (row 13 is frozen pane header column), and extends to a row that is different every week depending on how many people happen to be in the list.

I found something from this link that looks very similar to what I need, but with no knowledge of coding, I have no idea how it should be tweaked: Merge Duplicate Rows Keeping Data In Same Columns

I know I'm asking a lot, but the amount of time this takes to manually go through hundreds of rows of patient names every week is incredibly time consuming, and I have too many other things to stay on top of at work for this to drag me down day in and day out.

View 5 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

Excel 2007 :: Macro For Merging Data Of Alternate Rows?

Jul 7, 2014

Currently I am using Excel 2007. I want to merge data of alternate row of a particular column in Excel.Also i want to remove merged row. E.g.

Name City Pincode
John Mumbai
John 400009
Carol Delhi
Carol 110001
Carol Pune
Carol 411079

I want output to be ----->

Name City
John Mumbai-400009
Carol Delhi-110001
Carol Pune-411079

macro/formula for the above requirement..?

View 11 Replies View Related

Matrix From Columns

Mar 29, 2009

I have to create a matrix from columns in this format:

A1 A2 1
A1 A3 4
A1 A4 6
A1 A8 4
A2 A5 2
A2 A8 3

with a lot of empty cells (here, for exemple A1,A6 & A1,A7 ) and (A1,A2) = (A2,A1)
I'd like to have :

_ A1 A2 A3 A4 A5 A6 A7 A8 .....
A1 0 1 4 6 0 0 0 4
A2 1 0 0 0 2 0 0 3
etc

The source data is in the attachment. Would it be possible to do it automatically?

View 3 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

Finding Row And Columns For All TRUE Values In A Matrix

May 21, 2013

I'm using multiple data tables to run sensitivity analyses on a very large model. At the end of the analysis, I've got a matrix that tells me which scenarios are TRUE or FALSE (based on multiple criteria). I'm looking for an elegant way to extract the combinations that give TRUE.

Column1
Column2
Row1
FALSE
TRUE
Row2
TRUE
FALSE
Desired Output
True Scenarios:
R1, C2
R2, C1

View 1 Replies View Related

SUMPRODUCT Of Columns Based On Matrix Position

Mar 20, 2009

I have created the following attachment to illustrate my problem. I have a square matrix -- say a 10x10 matrix (with data in B2:K11, and column headings on row 1, row headings on column A). I would like to get the sum of products of two columns in the result section below -- cells B14:K23, based on the position of the formula.

For example, cell B14 is the 1,1 item of the matrix so I would like to get the sumproduct of B2:B11 and B2:B11 (or SUMSQ(B2:B11) in this case); alternatively cell I16 is element 8,3 in the matrix so I need to get sumproduct of I4:I11 and D4:D11.
I tried the following formula using the OFFSET function (for cell B14 which is then copy/pasted throughout B14:K23)

View 5 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 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 View Related

Merging Rows

Dec 3, 2009

I am looking for some tips to merge the 2 rows into 1 row with all data transferred. I have tried the following:-

=IF(MOD(ROW()/2,1)=0,INDEX($B$1:$B$100,ROW()/2),INDEX($A$1:$A$100,INT(ROW()/2)+1))
However, it returns "0" when cell is empty. Your help is greatly appreciated. Attached a file for better understanding.

View 10 Replies View Related

Moving Data From Rows To Columns And Delete Repeated Rows

Apr 16, 2014

removing duplicate rows and move other data frm rows to columns.xlsx.

I am attaching a sample excel sheet showing what I need to do.In the first tab, I have a list that includes duplicate rows (first column only). I want to remove those duplicate rows but I don't want to lose the data in the following columns which can be unique or duplicates as well.

see the desired result tab in the sheet to get an idea of what I am looking for as the end result.

Keep in mind that the actual source file I am working with could have up to 50000 row, and the expected results could be around 2000 rows. So nothing can be done manually.

View 5 Replies View Related

Merging Duplicate Rows To One

Oct 18, 2005

I have a list that contains multiple data that I would like to merge in to one. There are varying duplicates.

Currently it looks similar to this:

Repid, Lname, Fname, States
000001, Doe John, CT
000001, Doe John, NY
000001, Doe John, GA
000050, Jones Mary, NJ
000050, Jones Mary, NY
000025, Presley Elvis, PA
000025, Presley Elvis, NY
000100, Doe Jane, NJ

I want to return the following:

Lname, Fname, Repid, States
000001, Doe, John, CT, NY, GA, NJ, CA, MA
000050, Jones, Mary, NJ, NY
000025, Presley, Elvis, PA, NY, GA, NJ
000100, Doe, Jane, NJ

View 9 Replies View Related

Merging Cells From Multiple Rows Into One Row

Jan 29, 2014

For my work I need to delete duplicate rows with the same name but keep the information in the (original) cell.

for example I have:

Cells: C D E F
Row 1 SoccerPremier League Holland
Row 2 SoccerPremier League Germany
Row 3 SoccerPremier League Poland Poland

I want to keep row 1 and add the data from row 2 & 3 with the data in the same cells so it will be:

Cells: C D E F
Row 1 SoccerPremier League HollandGermanyPolandPoland

As cells D, E, F are empty I would say you can just merge the 3 rows by selecting them and merge.

View 3 Replies View Related

Macro For Merging Multiple Rows To One Row

Aug 19, 2013

number
string
string
string

date

string
string
date

string
string
string

string
string

string

View 9 Replies View Related

Merging Multiple Rows Into One Row With Certain Cells?

Oct 3, 2013

I am trying to do the following:

I have about 50,000 rows of user info, something like the below -

ID
First Name
Surname
Description

1
John
Smith
Associate

2
Rob
Page
Associate

What would be the best way to go about this?

View 3 Replies View Related

Move Rows Of Data From Several Columns But Not All Columns?

Dec 18, 2013

Column A is numbered 1 -100 successively Column B thru D contains data that goes with the assigned number in column A. I need to be able to move rows of data in column B through D to a different set of rows all at the same time (not one cell at a time) without disturbing the set numbers in column A. And with that, have all the other rows of data automatically adjust accordingly(not to be deleted or replaced).

View 1 Replies View Related

Counting Fully Filled Rows In Filtered Matrix?

Jan 22, 2012

I'm trying to calculate the number of rows in a matrix that have a complete row of data. I would noprmally do this using nested if formuals and a count function, but the columns of the matrix are filterable so the count won't work.

Is there a combination of functions that will adapt as I filter data?

View 5 Replies View Related

Creating Adjacent Matrix From Incidence Matrix

Aug 26, 2007

I have a data chart on 200 people with overlapping membership in 20+ groups, represeted as binary (1=member, 0=notmember), for example: ...

View 9 Replies View Related







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