Change Multiple Columns Into Multiple Rows

Oct 10, 2008

I have a large spreadsheet that contains and employee number and then up to 5 entries of Dependants (each has its own column). What I need to do is have 1 for each of the dependant information instead of 5 and have multiple rows for the employee if they have more than one dependant.

At the moment I am sorting the data by dep 1 name, pasting the data into a new sheet, deleting all the dep 1 stuff, sorting by dep name 2 and pasting that.........etc.

It doesnt take too long to do but I just wondered if there might be a quicker way as this will be something I will need to do more often.

View 2 Replies


ADVERTISEMENT

Multiple Ccolums/rows To Get Data From Multiple Columns/rows (vlookup)

Jan 15, 2010

I have created a spreadsheet to show some reports and I wanted to serch for some datas which overloops themeselves. If you can have a look at a test file I attached you will see the full picture. I have 2 tables, where the 2nd one is on the right side of the 1st one. 1st table:..............

View 3 Replies View Related

VBA Code To Copy Selected Multiple Columns To Multiple Rows In Excel

Mar 13, 2014

I want to to copy selected columns of sales data into rows organized by salesperson. I have just started out with VBA and find that I cannot do it myself.

My original data are in the form of the following:

invoice_no
product
sales
qty
total

[Code] .....

I want to display the data in another sheet in the following format:

sales_a
sales_b
sales_c
sales_d

[Code] ...........

View 2 Replies View Related

Copy Rows Meeting Multiple Criteria On Multiple Columns

Dec 18, 2006

1. Copy data from original file (I do not want to do anything in the original file) into the spreadsheet (Target worksheet)where the code should run.
2. In sheet 1 of Target Worksheet, there are 2 columns which I need to set criterias on namely Column D and Column L
3. In Column D, I want to specify 3 criterias namely A, B and C
4. In Column L, I want to specify 5 criterias namely London, Frankfurt, New York, Sydney and Tokyo
5. If Criterias in 3 and 4 are met, copy all rows into Sheet 2 of Target Worksheet

View 9 Replies View Related

Delete Multiple Rows :: Match In Multiple Columns

Oct 29, 2008

I need to find all rows that have columns that match in all 3 of the columns.

I then need to delete all but the last row in each "group" of rows.

like:

1 q w e
2 q w r
3 q w r
4 q w r

Delete rows 2+3

View 9 Replies View Related

Sum Multiple Criteria In Multiple Columns And Rows

Jul 10, 2013

create a formula,for multiple criteria with multiple results in rows.

[URL]

View 1 Replies View Related

Multiple Columns To Multiple Rows

Jan 14, 2010

I have a spreadsheet that has ONE row for each day of the year and FOUR columns for each day. Some days use 1, 2, 3 or 4 columns. I need to turn these into rows for each Column.

Here is an example of the detail data: ....

View 9 Replies View Related

Move Data From 2 Columns Into Multiple Columns At Each Change

Feb 2, 2008

I am trying to write a macro that will take data from column A and use it as a column header in column C, and then take the corresponding data in column B and list it under the column header in column C. When the value in column A changes it will move on to column D and then list the corresponding data from column B underneath it in column D until all of the data in the 2 columns is spread across the worksheet. Example:..............

View 2 Replies View Related

Worksheet Change Looking For Multiple Columns

Apr 27, 2007

I have the following code to check and see if the user enters a number into a specific column. If so, and if the number is too high, the routine will automatically lower it to an acceptable number. However, I am wondering, is there not a simpler way to do this, besides using a bunch of OR statements?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Or Target.Column = 4 Or Target.Column = 6 Or Target.Column = 8 Or Target.Column = 10 Or Target.Column = 12 Then
Application.EnableEvents = False
If IsNumeric(Target.Value) = True And Target.Value >= 105.6 Then
Target.Value = 105.4
End If
Application.EnableEvents = True
End If
End Sub

This works just fine, but it seems kind of sloppy to me.

I was wondering if there might be a way to put the column numbers into an array, and then simply check if the Target column equals any number in the array. But I do not know how to do that without looping.

View 7 Replies View Related

Change Value Field Settings For Multiple Columns At Once

Aug 3, 2012

I have a big table with lots of columns for which I want a pivot to sum the data in the columns. Most likely because there are lot of blanks in the data Excel automatic makes a count of the data when I drag it into the pivot table. Now I have to change for each column one by one the Value field settings from count to sum. Is there a way to change the Value field settings for multiple columns at once from count to sum? If not can I somehow change the settings of the pivot table so default the Value field settings will be sum when I drag it into the pivot table?

View 4 Replies View Related

Multiple Rows And Columns

Jun 19, 2009

I have a sheet that is organised as follows:
a) Column A contains 50 names, all unique. R1C1 heading is "Name".
b) Column B to Column H are for some text that can contain special characters, numbers, spaces etc.
c) R1C2 heading: "Day 1"
d) R1C3 heading: "Day 2". and so on till R1C8 having heading as "Day 7"
e) All the cells are filled in the specified range as specified in (b) above.

In summary, each of the names will have 7 different tasks associated with it in the row. These tasks can repeat for all the names.

For example: Consider the below set of data for 3 rows and 7 columns (spaces are used as column delimiter. In reality, spaces can form part of the tasks for each name):

Name D1 D2 D3 D4 D5 D6 D7
-------------------------------------
aaaa T1 T1 T1 T2 T3 T4 T5
bbbb T1 T2 T3 T3 T3 T4 T5
cccc T1 T4 T4 T4 T4 T4 T4

I hope its clear till this point.

Now my requirements are as follows:
a) Find out all unique elements from the set of tasks across all days and list them from 100th row onwards with each unique entry in new row.
b) For each of the unique tasks, list the names that have worked on that task in the same row.

For example, the output should be as follows:
T1 | aaaa, bbbb, cccc
T2 | aaaa, bbbb
T3 | aaaa, bbbb
T4 | aaaa, bbbb, cccc
T5 | aaaa, bbbb

The character "|" above is used as a seperator for columns.

View 5 Replies View Related

How To Add Multiple Columns And Rows

Oct 21, 2013

Date
Name
Diesl
Petrol
Oil
Kerosene

[Code]...

Here from the above table i want to get the complete sum of all numbers corresponding to a specific date and person..

I used sumifs which looks like this: =SUMIFS(C2:F10,B1:B10,A12,A1:A10,B12)

But that doesn't work, should i resort to using multiple sumifs?

View 1 Replies View Related

Macro - Copy Multiple Rows Of Data To New Sheet On Change

Dec 23, 2008

I have a multiple rows of data, but one column which is K has data like:

1020
1020
1020
1020
1020
1030
1030
1030
1030
1050
1050
1050

What i want my macro to do, is whenever that number in column K changes to copy all the data in row A thru K to a new sheet.

View 9 Replies View Related

Transpose Multiple Columns Into Rows?

Nov 20, 2013

I have following information. I need to transpose the columns into rows

C
10001

V
1000

V
1001

V
1002

C
10002

V
1001

V
1003

C
10003

V
1001

V
1003

V
1004

The expected result should be like
10001 1000 1001 1002
10002 1001 1003
10003 1001 1003 1004

View 4 Replies View Related

Formatting Multiple Rows To Columns?

Mar 9, 2014

I'm trying to move multiple ROWS to COLUMNS. My data looks like this:

ID CLASS_NBR1 UNITS_TAKENGRADECLASS_NBR2UNITS_TAKENGRADE CLASS_NBR3
000555 688 1 A
000555 567 3 B
000555 888 1 C
000555 999 1 B
000555 889 3 C
101999 889 3 A
101999 567 1 B

I have between 4-9 rows that I'm trying to move to columns. I'm attempting to move data for each student on one row, currently each student has between 4-9 rows.

I've tried so many different formulas and MACROS that didn't work I started doing this one by one (manually) but I have too many rows and it's taking too long.

View 14 Replies View Related

Separating From Multiple Rows Into Columns

Mar 25, 2014

I have an interesting problem I've been presented with and rather than try formula after formula I thought I'd propose it to you all (see attached sheet).

I have 7269 rows with 1930 distinct claims. I need to pull out the Primary issue (done that) then across from it put in each and every secondary issue (from col C). The largest # of claim lines is 89 (see E1). So in theory I need to find that claim and put all the secondary issues in the next 89 columns from row 4990 beginning in col H.

I've done a couple examples of what I'm looking for in rows 2, 4 and 5 and 8 but beyond that ...

So every place where there is a value in col F I'd like the list of secondary issues in the same row.

View 4 Replies View Related

Range With Multiple Rows And Columns?

Mar 6, 2014

I am using the following range to copy data from Column A (from rows 3 on the the last row). How can I include Columns B and C into this range also, but still using Column A to find the last row?

[Code] .....

View 9 Replies View Related

Average By Multiple Columns And Rows

Apr 20, 2007

I have a file that I want Column D to average by column A and C. I also want to ignore any rows that dont have data in column B in the average. See sample file.

View 5 Replies View Related

Compile List From Multiple Columns / Rows

Jan 17, 2014

I want to essentially create a meal plan and then have my shopping list generated from the ingredients columns/areas into a separate sheet for printing. I need shopping list generation. I have attached a sample of what I am currently working with but still needs a lot of tweaking. I am not the most advanced excel user as you can see but I continually try to improve upon all of my work.

Test_Meal_Plan.xlsx

View 2 Replies View Related

Averaging Multiple Columns / Rows If Two Criteria Are Met

Apr 30, 2014

I have a dataset consisting of concentrations of parameters (alpha and beta) at different locations over multiple years. I've included an example dataset here.

I need to calculate an average and standard deviation for each parameter that spans multiple locations and years (but not all locations and years).

Example 1: Calculate the average and standard deviation of alpha values from years 2009 to 2012 at locations A and C.

Answer should be: Average of {0.84, 0.47, 0.27, 0.14, 0.36, 0.65, 0.66, 0.85} is 0.53. Standard deviation of {0.84, 0.47, 0.27, 0.14, 0.36, 0.65, 0.66, 0.85} is 0.26.

The real dataset is large, including 7 different parameters and more than 30 locations. I need to perform these calculations for many parameters, so am looking for a formula (or array formula) that will do this in as little cells as possible. Can this be done by formula or will I need a macro?

Location
Parameter
2008
2009
2010
2011
2012
2013

LocA
alpha
0.24
0.84
0.47
0.27
0.14
0.33

LocA
beta
4
9
9
8
2
9

LocB
alpha
0.24
0.33
0.85
0.54
0.56
0.65

LocB
beta
8
7
6
7
2
9

LocC
alpha
0.24
0.36
0.65
0.66
0.85
0.92

View 3 Replies View Related

How To Delete Multiple Rows But That Match In Columns

May 22, 2014

ertret.jpg

I am trying to delete rows that contain the same addresses BASED ON COLUMNS.

Do you see how in columns C and D they all match EXCEPT for row 4? How do I delete allllll the rows that when c and d match.

I want to keep the ones that DO NOT MATCH.

P.S I HAVE 4000 ROWS I WOULD HAVE TO GO THROUGH

View 1 Replies View Related

Sumproduct For Multiple Criteria Across Columns And Rows

Jan 10, 2014

I've not used SUMPRODUCT previously and can't understand how to get results for the attached.

I've tried SUMIFS but it doesn't work because I'm looking down columns and across rows, I'm assuming.

I've attached a summary of what I'm trying to achieve. I want to sum all costs with an R,P,I,G, etc. in column C for December '13 (E3) in the top table.

The second table is actually in a different sheet but is the source of the data I need added.

Sumproduct P&L.xlsx‎

View 11 Replies View Related

Scroll Multiple Rows / Columns At A Time?

Mar 15, 2013

I wonder if Excel allows scrolling many columns or rows at a time. That's right, I have a worksheet with frozen panes by "D2" cell; then there are sets of 4 columns for each day of the month (for example "D:G", "H:K" and so on). I need when I scroll horizontally, that every first column of the set to snap at the edge of pane at once. In other words, is any elegant way that usual scrolling pace of one column/row at a time to be changed to 4 at a time?

View 3 Replies View Related

Transposing Multiple Rows And Columns To One Column

Jul 27, 2013

I have a spreadsheet that calculates data in multiple columns (A:AN) and each column has 27 rows of data called "Sheet 1". As it stands, the spreadsheet goes out very far. On "Sheet 2" I would like for all of the data in "Sheet 1", columns A:AN to be "transposed" to "Sheet 2" in column A only. So, column A on "Sheet 2" would have the data from "Sheet 1" A1:A27, B1:B27, C1:C27, so on and so forth. I would like for the formatting to maintain the same order as well.

An example would be:

Sheet 1
Column A B
Row
1 Green Red
2 Blue Yellow
3 Black Cyan
4 Pink Magenta

[code]....

View 3 Replies View Related

Applying Formula Across Columns For Multiple Rows?

Mar 3, 2014

I have a formula that I wrote in B2 and then applied vertically to a large list of rows (100+). I applied it vertically by simply double-clicking the cell with the formula, and it cleanly applied the formula to every row in Column B.

This formula now needs to be applied horizonally across columns, for each respective row. I need a way to do this in one swoop, as it would be very time consuming to manually drag each cell in column B.

I've attached a sample spreadsheet with a simple formula just for the sake of an example.

View 3 Replies View Related

How To Fill Color For Multiple Rows And Columns

May 21, 2014

I want to write a macro to fill color in row 8 of the worksheet as solid black . Also columns P,T,AP,BC ,BE and BG should be filled solid black and width should be 1 for the row and the column.

View 7 Replies View Related

Multiple Lines To One Line (Rows To Columns)

Aug 12, 2014

I have is a list of the over 500 people with one line per college degree. Some have up to 4 or 5 degrees. Each have SSN as unique identifier.

I need to consolidate into one like per person with multiple columns one for each degree.

See attached : Test Degrees.xlsx

View 6 Replies View Related

Combining Like Items Across Multiple Columns And Rows

Mar 3, 2014

[URL]...I was trying to use this thread's solution macro on my sheet that is only consolidating columns A, B, and C with a couple thousand lines and I've had it running for over an hour now. There hasn't been any errors but is there any particular reason that might be causing it to take longer than it should aside from having to go through a few thousand rows?

Basically, I only want 1 Version of data in Column B and any other rows that has a match in column B, i want to consolidate unique data from Columns A and C.

A B C
AB 123 AAC
BA 131 AAC
AB 222 CAA

Res:
A B C
AB 123, 222 AAC, CAA
BA 131 AAC

View 2 Replies View Related

Hiding Rows With Zeros (Multiple Columns)

Sep 26, 2008

I have values in column D & K. Now i want to hide the rows with Zero's if they are in both D & K. Now if there is a Value in K and a Zero in D i don't want to hide that row.

Could someone please help if this is possible. I attached the code below for hiding lines according to one column:

View 12 Replies View Related

Combining Like Items Across Multiple Columns/Rows?

Jun 9, 2009

This isn't absolutely necessary, but being able to do this easily would cut down on a lot of tedious jobs for me. Let's say I have a set of data like this (I tried to space it out as best I could to give the idea):

View 3 Replies View Related







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