Merge Columns & Create New Row Layout

Aug 7, 2008

i have a table looking something like this

----A--------------B---------------C----------------D-------------
1---Destination-----Country_Code---Area_Code------ Price_Per_Minute
2---Armenia--------99--------------58, 59, 566------0.098
3---Jordan---------96-------------79,78,77,2--------1.023
4---UK-------------44-------------------------------0.50
5---UK-------------44-------------79,78-------------0.48

what i actually need is to turn this table into 3 columns table, the first column is the destination, the second one is the destination code which consists of the concatenation of the country_code and the area_code columns, and the third column will be the price_per_minute, the main problem is that the Area Code column contains many values and each value should be attached to the country_code and then i need to create more rows for each country, so the my table should look like this:

----A--------------B---------------D-------------
1---Destination----Code-------Price_Per_Minute
2---Armenia-------9958-------0.098
3---Armenia-------9959-------0.098
4---Armenia-------99556------0.098
5---Jordan--------9679-------1.023
6---Jordan--------9678-------1.023
7---Jordan--------9677-------1.023
8---Jordan--------962--------1.023
9---UK------------44----------0.50
10--UK------------4479-------0.48
11--UK------------4478-------0.48

View 5 Replies


ADVERTISEMENT

Merge Two Columns To Create 1 Column With All The Data

Mar 12, 2014

I have 2 columns full of information feeding from 2 different sources on our internet database

I would like to collect this information into 1 column to create a drop down list

As these are expanding tables feeding from the internet I don't think copy and paste into one column will work (I'm not sure about this but I would prefer a formula to avoid any issues).

View 4 Replies View Related

Data Table Layout Change For Mail Merge

Jun 17, 2013

I have responses from a questionnaire in the format below, and need to mail merge to a word doc.

name
subject
teacher
grade
comment

[Code]....

If I'm right I need it in the following format for a mail merge.

name
subject
teacher
grade
comment

[Code]....

I need to get this fixed by 5 pm tonight, otherwise a foresee a night of copy and paste ahead. I thought pivot tables might work, but alas no, it only tells me the number of the comments i have.

View 7 Replies View Related

Create Organized Layout For Reporting

Sep 11, 2007

I have a list of all my products in the "Price Listing" sheet organised by a 3 level tree structure (small range of products shown in excel file attached).

I am trying to create a way of getting this list into an organised way (via macro) so that I can print this off and give to my customers. Sheet 1 shows an example of how it might look after the macro is run using the tree structure. I have tried to use pivot tables with no success.I am currently creating this manually each month and updating all new products, then running the prices for each customer but it is taking far to lonng to create the master sheet.

Different customers have different prices and I want to create a price list so I can get someone in the office to output the file to excel, run this macro and create the file without me having to put alot of work into each one, or even be in the office while they are creating this.

Is it possible to create a table of contents for the completed result using either seperare sheets, text formatting

View 9 Replies View Related

Layout/Transpose Data Rows Into Columns

Jun 17, 2008

I have a following table:

A B C
1 City Name List
2 NY Peter 11; 23; 12; 11; 14
3 Toronto John 24; 25; 87

How can I, in a separate worksheet, create a following table?

A B C
1 City Name List
2 NY Peter 11
3 NY Peter 23
4 NY Peter 12
5 NY Peter 11
6 NY Peter 14
7 Toronto John 24
8 Toronto John 25
9 Toronto John 87

View 4 Replies View Related

Merge 2 Tabs Into 1 Spreadsheet To Create Pivot Table?

Jul 9, 2014

I would like to combine 2 tabs into one spreadsheet. I was not able to do so, because it exceeds the max rows allowed in excel. I have installed powerpivots but not sure how to combine data using powerpivots.

View 3 Replies View Related

Merge The New And Old Columns?

Mar 31, 2014

I have two columns, which have different length and in which there can be three types of data:

The data also in the first and the second column, so it is an old data.Second is, that the data is in the first column, but not in the second, so it is old, but it was solved.The third type is, when the data is in the second column, but not iin the first, so it is new data.

My task is to regularly update the list (so I have to merge the new and old columns), to see in which conditions the data is, but not to delete any of them (only the recurring, so if it is old, then it must not be listed two times).

View 1 Replies View Related

Merge Columns A And B To C

Feb 7, 2009

I have a need to get the data from columns A and B and transfer to C but one line from A and second line from B and this successively until the end of A and B. They will have the same number of lines.

View 3 Replies View Related

How To Merge Two Columns Together

Jan 26, 2013

How I can merge two columns together and keep the data in both columns?

I have an address issue when the data was converted to excel it put the numbers part of the address in column "C" and the street name in column "D".

View 2 Replies View Related

Merge 2 Columns

Sep 12, 2008

i am trying to create a macro that merges two columns in excel.

View 9 Replies View Related

VBA Merge Multiple Columns

Feb 15, 2014

I'm currently working with the "Original"sheet shown on the attached file. From this, you will see that there is generic information contained in columns A:D then columns containing a month date and the associated FTE figure.

What I'm trying to do is as follows:

-Insert two columns after the "Investment Role" column, with column headers of "Period" and "FTE"
-Then, for each month column for each record, create a new row one under the other, copy the values in columns A:D and paste them into the newly created row,
-Then move the Date value for each month and the associated FTE figure and put these in the "Period" and "FTE"columns.

I've attached a file showing the 'Original' dataset and 'Desired Outcome' to look at.

Although the values in all of the fields may change month on month, and the number of rows may change, the number of columns will remain the same.

Merge Columns Test.xls‎

View 5 Replies View Related

Merge Values From 2 Columns Into 1

Jul 9, 2009

I'd like to perform a merge of value between columns D and E.
So if D9's value = 2 and E9's value = 15
then the result will be
E9=215

I've tried the following but I haven't gotten it to work.

View 7 Replies View Related

Merge Two Columns To One Filled Column?

Jul 23, 2014

I have two columns that are the same field, both Middle Name. The first column does not have every single row filled and the second column has every row the first column does not have and vice versa. I want to merge them to one filled column.

View 2 Replies View Related

Concatenate Function To Merge Certain Columns

Aug 14, 2009

I have a worksheet with 10000 plus entries in ten columns From K to T. I wish to have a macro with Concatenate function to merge certain Columns from these ten columns, in one column with help of input boxes which may ask inputs, about range (from and to ), and number of digits to concatenate in the required column. I use Excel 2003 XP in work but an example is 2007 attached.

View 5 Replies View Related

Merge And Sort Data Across Columns?

Dec 19, 2012

So I have several columns of data that have a location and then some numbers after it in additional columns. I need to be able to sort it so that all locations that are in both location columns are sorted first, and then any locations that are in one column but not the other column follows. Also the data associated with each column that follows the location needs to remain next to the location. Many times the columns will be of highly disportionate lengths too.

I've attached an HTML table so you can get an idea of what I am looking at, except what I'm dealing with is like.... hundreds of cells long.

I want to be able to turn this:

HTML Code: 

<table border="1" bordercolor="#FFCC00" style="background-color:#FFFFCC" width="100%" cellpadding="3" cellspacing="3">
<tr>
<td>A</td>

[Code].....

And in this 6 row example, I'd actually only be interested in the 4 letters that were in both columns. If it makes it easier, I would be okay with discarding the last rows, but would rather just have them sorted at the end, so I could check if....for example there were two different spellings of the same thing, and they should actually be included.

View 3 Replies View Related

Merge Rows Into Columns With More Than One Variable

Nov 12, 2007

I have been scanning web forums for days and have not found a solution to my question. I have found similar, but unfortunately am not clever enough with Excel VBA to adapt.

I have two columns of data which I want merged into rows (a) by survey number and (b) by time slots. The data I have is similar to:

Survey No.....Results
12345...........9am-12pm
12345...........3pm-6pm
12345...........No answer
56789...........6am-9am
56789...........12pm-3pm
56789...........6pm-8pm
56789...........10pm-12am
.....................

View 12 Replies View Related

Take Two Columns And Merge Them Without Losing Data

Nov 14, 2008

I've got two columns:

A, B
abc, def
qwe, rty
asd, fgh
zxc, vbn

And I need to bring these together in one column so it looks like this

C
abcdef
qwerty
asdfgh
zxcvbn

View 2 Replies View Related

Select Columns To Merge Within For Loop

Nov 18, 2011

My issue is I want to use a for loop to go through a spreadsheet. Within the for loop if a certain condition is true I want it to select all columns from A to AW and merge them together.

Example of what I have so far:

Sub MergeHeaders
dim i as Intger, LastRow as Integer
LastRow = Cells(Rows.Count, "CP").End(xlUp).Row
For i = 1 to LastRow
'Check if the leading character is a letter, not number
If Asc(Cells(i, "AP")) > 64 Then
'I want select from A to AW only for current row i, then merge
end If
next i
end sub

View 1 Replies View Related

Merge Sheets - Put Data Next To Columns?

Jun 7, 2012

I use the following code to merge all data a "master sheet", but I want to take the data and put it next to each other on the "master sheet", not under. For example, my range of data is "A1:D15" and I want to copy from all sheets. So my first paste would be A1:D15, then the next should be E1:H15.

I would like the code to find the used range, like it does currently.

Code:
Sub Merge()
Dim ws As Worksheet
ActiveSheet.UsedRange.Offset(0).Clear

[Code]....

View 1 Replies View Related

How To Merge Columns With Same Data In Excel

Sep 27, 2013

I've found macro, which merge rows with the same data

Example:

BEFORE:

january
1st

january
2nd

[Code] ........

Macro:

Option Explicit
Sub MergeSame()
Dim r As Range, c As Range
Dim i As Long, j As Long
Set r = Range("a1", Cells(Rows.Count, "a").End(xlUp))

[Code] .....

But i need use the same procedure for columns, it means:

BEFORE

A
B
C
D
E
F

january
january
january
february
february
february

AFTER

A
B
C
D
E
F

january
february

I've tried modified macro marked above, but without success...

View 3 Replies View Related

Script To Merge Few Columns From Different Worksheet

Oct 26, 2013

I have 13 different worksheets with the same header columns, i would like to merge into one creating new work sheet. All the data in column A should keep on adding on the column A from all the worksheet and from column N going across. I have more than million records on each sheet

I have dropped the file on [URL]... for example

View 8 Replies View Related

Merge Bill Of Material Columns

Dec 5, 2009

I use CAD software that generates Bills Of Material. I cut & paste these to an Excel template that has column headers in row 3, for example:

U3 = Item name
V3 = Manufacturer
W3 = Reference_item_name
X3 = Reference_item_ID

Starting from row 4, I would like to add the content of columns V, W and X to column U, separated by comma's. No superfluous comma's should be added when columns are empty. It would be nice to have a macro that uses the row 3 column names, so it still works if someone changes the column order.

View 9 Replies View Related

Auto-Merge Dates In 3 Columns And Then Auto-sort Merged Columns

Jan 10, 2012

I have dates values in 3 columns.

A1 - Header - "Holidays in XXXX'
B1 - Header - "Holidays in YYYY'
C1 - Header - "Leaves by YOU"

A2:B11 have static dates consisting of 10 dates in each column.
C2:C11 - the user may enter any date at any point of time.

I would like to auto-merge the dates in all the 3 columns (A2:C11) in a single column say D2:D31 and then the system should auto-sort the column based on dates in any one order. So as soon as the user enters a value in say cell C2, all the 10+10+1 dates should get sorted.

Also the constraint here is a user may not apply all 10 leaves at in a year. So many of the cells may have blank values.

View 1 Replies View Related

Merge Based On Columns If There Is Duplicated Data?

May 19, 2014

I have a large volume of data and i need to manage and organize somehow.

Example:

A B C D E
CHRIS AN204 2005 Apple Green
CHRIS AN204 2005 Apple Red
CHRIS AN205 2005 Apple Yellow
TOM AN204 2006 Apple Green

This should look like this

A B C D E
CHRIS AN204 2005 Apple Green, Red
CHRIS AN205 2005 Apple Yellow
TOM AN204 2006 Apple Green

I manage somehow to find a VBA code that will merge my rows, but it's not working as it should. It's losing data.

Also in a cell it can happen to have more than 255 characters.

View 1 Replies View Related

Merge Several Columns If Duplicates Exist In Column A

Apr 13, 2008

I have got a wordlist in worksheet "original" which looks like:

Before: [Code] .....

I need a macro which merges the columns B,C,D,E and F depending on if there are duplicates in Column A or not. If there are one two or more duplicates in column A,then those should be deleted and only one of them should remain in column A. The members of deleted duplicates in column B,C,D,E and F should be merged together. No duplicates should be made by the process of merging. Each member in column B,C,D,E and F has to be unique. The results are supposed to be put in worksheet "new".Columns B,C,E and F should be merged through signe "/". And Column D should be merged through signe ",".

After: [Code] ......

The macro must be able to deal with very large lists. biger than 200 000 words in column A

Here is the excel file containing the example : excel file

View 14 Replies View Related

Merge Columns B,C,D,E And F, If Duplicates Exist In Column A

Apr 13, 2008

i have got a wordlist in worksheet "original" which looks like:

Before:

View 14 Replies View Related

Merge Columns Without Losing Data Conditionally

Oct 30, 2013

My data is in column A , Column B and Column C, where are column C has a uniuqe value for certain rows.

Based on these uniques value, column A and Column B should be merged to singe Row without loosing data.

MY Data:
Source address
Destination address
Destination Port
Application
IP Protocol

192.168.1.2
192.168.250.10
53
dns
udp

[Code] .......

Result expecting is :

Source address
Destination address
Destination Port
Application
IP Protocol

[Code] .......

View 7 Replies View Related

Moving Duplicate Rows To Columns For Mail Merge

Jul 28, 2008

to prepare an excel spreadsheet for a mailmerge but as all of the info for 1 recipient needs to be in columns instead of rows. I need to convert 2 columns' data into columns but only when there are duplicate invoices, see below;

View 9 Replies View Related

Mail Merge Of Information In One Cell To Numerous Columns

Feb 23, 2010

I have a report that the name and address was put into one cell. I need to put this into numerous columns so I can do a mail merge.

View 9 Replies View Related

Excel 2013 :: Merge ALL Cells With Same Value In A Sheet (rows And Columns)

May 11, 2014

We are doing a graduation project on an international airport, consist of scheduling flights on check-in counters automatically.

long story short, we ended up with an excel sheet like this: Screen_Shot_2014-05-11_at_4.png

Were y-axis are the check-in counters and x-axis is the timeline horizon (cell per 5-minutes)

I will do a VLOOKUP, to change each flight number to it's ID from an other sheet.

But the problem is that i want to AUTO-MERGE all cells with same value, because they represent one flight! and if i shortened the column width i can't see anything.

I want it to be like this: (i've done this manually, and it's VERY time consuming with errors because we have to do it for all days.)

Screen_Shot_2014-05-11_at_46FDQO.png

I googled for days, i only found Visual basic commands i guess? that only merge same rows. and they were poorly made. beside that it didn't work properly. Method to do it automatically?

EXCEL 2013

View 3 Replies View Related







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