Creating Horizontal Summary Table From Consecutive Vertical Data Lists?

Jul 17, 2014

I have consecutive vertical lists that each have a different number of instances and I'm trying to create a horizontal summary. Here is an example:

Vertical lists:
Mary
red 2
blue 11
John
yellow 5
red 7
blue 8
Susan
red 9
green 3

Desired Summary table:
Mary John Susan
yellow 5
red 2 7
blue 11 8
green 3

Is there a way to do this using a pivot table or formulas, instead of manually?

View 3 Replies


ADVERTISEMENT

Convert Vertical & Horizontal Data To 2 Lists

Mar 13, 2008

I was wondering is there a way to convert the format of one excel spreadsheet to a different format in another. I have attached a spreadsheet which illustrates what I'm trying to convert from and to. Perhaps VB Script? I have no idea how this could be resolved.

View 3 Replies View Related

Sum Horizontal Data In Vertical Summary Where Data Matches?

Jul 9, 2014

I have a page of data that i need to summarise/calculate, i thought sumif would be the correct formulae but i can't get it to work...

Sheet 1 - Data Recomds Emp Name, Weeks 1-52 showing no of hours to adj
Name
WK1
WK2
Wk3
WK4
WK5
WK6

Oliver
-1.5

[Code] .....

Sheet 2 - Summary by month - to Calculate the no of hours for the period per employee

Name
Month 1

Avery
Require Sum for employee Avery Wk 1-4

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

View 5 Replies View Related

Copy Formula Down / Across Taking From Data Table With Vertical And Horizontal Parameters

Mar 6, 2013

Excell file eWorked Example.xlsx

I am trying to set-up a formula that needs to be copied down and across and draws upon a data table that has a horizontal and vertical parameter that needs to be checked for the formula to return the right answer.

I have tried all manner of Index/Match, Offset etc but can only get one of either the vertical, or horizontal parameter working.

I have attempted a formula which is

=INDEX('PIP Input'!$C$17:$C$29,MATCH(H$1,('PIP Input'!$H$17:$H$29),0),MATCH($B4,'PIP Input'!$H$15:$L$15,0))

('PIP Input'!$H$17:$H$29) is column dependent on MATCH($B4,'PIP Input'!$H$15:$L$15,0)

If $B4 returned a different result from ('PIP Input'!$H$17:$H$29) then it would need to be ('PIP Input'!$I$17:$I$29)

How to nest the Horizontal Match inside the Vertical Match, such that the Vertical Match changes column depending on $B4?

View 1 Replies View Related

Horizontal And Vertical VLOOKUP In A Table?

Jan 27, 2014

I have to fill an invoice template, whereby I need to lookup the unit price in a pricing sheet, based on the effective date (horizontally placed) and the specific tasks to be invoiced (vertically placed).

The effective date cell (B1) is the one that can be changed, the formulas in the unit price cells (yellow highlighted) then needs to look up the price for that specific task in the table on the tab "pricing sheet".

I was trying something with INDEX / MATCH, but can't get it to work.

View 4 Replies View Related

Lookup Table Using Horizontal And Vertical Criteria?

Mar 6, 2014

comp1.PNG

Attached is a screenshot. Basically I need to know that if PersonA has say 13.74 CPH and $776 DPH - what the incentive would be ($575)...

View 3 Replies View Related

How To Change Horizontal Table To Vertical List

Jul 22, 2013

I need to change data from a horizontal table into a vertical list? example.xlsx

View 1 Replies View Related

Arranging Vertical Data To Horizontal

Dec 25, 2013

I have not found a suitable solution for arranging vertical data horizontally using VBA

I have number of data in column B arranged vertically in sheet1 how can we arrange the data in sheet 2 column B row 3 horizontally.

The vertical data keeps increasing when new data is added, so VBA code should search to the last line in Sheet1 column B.

All duplicate entries should not be repeated when arranging horizontally.

View 9 Replies View Related

Convert Data Horizontal To Vertical

Sep 5, 2009

i have a data in columns and would like to convert into rows

e.g.
11 A B C D E F G H
22 1 2 3 4 5 6 7 8
33 2 3 4 5 6 7 8 9
44 3 4 5 6 7 8 9 0

and result should be
A 22 1
A 33 2
A 44 3
B 22 2
B 33 3
B 44 4
C 22 3
C 33 4
C 44 5
and so on ..

View 6 Replies View Related

SUMIFS - How To Sum Data Using Both Horizontal And Vertical

Jul 20, 2012

I have a huge data table, and I just can't seem to find a way to sum the numbers the way I want.

Here's an example of the table:

___ A B C A B C A B C A B C A B C
Jan 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5
Jan 3 4 5 6 3 4 5 6 3 4 5 6 3 4 5
Jan 3 4 5 6 3 4 5 6 3 4 5 6 3 4 5
Jan 3 4 5 6 3 4 5 6 3 4 5 6 3 4 5
Feb 3 4 5 6 3 4 5 6 3 4 5 6 3 4 5
Feb 6 3 4 5 6 3 6 3 4 5 6 3 6 3 4
Feb 6 3 4 5 6 3 6 3 4 5 6 3 6 3 4
Feb 6 3 4 5 6 3 6 3 4 5 6 3 6 3 4
Feb 6 3 4 5 6 3 6 3 4 5 6 3 6 3 4
Mar 6 3 4 5 6 3 6 3 4 5 6 3 6 3 4
Mar 6 3 4 5 6 3 6 3 4 5 6 3 6 3 4
...
...

But, in essence, I need to sum together every value that's in January AND is in a column labeled "A".

I could just write up a bunch of "sumif=" functions but I feel like there's a simpler way.. I tried using "sumifs=" but I don't think it can handle horizontal AND vertical criteria...

View 14 Replies View Related

Transpose Vertical Data To Horizontal?

Apr 18, 2013

I have the attached worksheet that I would like to know how to move the data from Vertical to Horizontal

Is there a set function to do this or does it require a macro?

View 5 Replies View Related

Make Horizontal Data Go Vertical

Dec 13, 2005

a_____b____ c_____ d_____ e______ f______ g_____ h_____ i
1 - 12/1--| 385 -| 15000 -| --etc--|| --etc--|| --etc--|| --etc--|| --etc--|
2 - 12/5--| 256- | 20000 -|
3 - 12/6--| 356- | 11000 -|
4 - 12/5--| -89--| -1000 --|
5 - 12/9--| 500- | 9000 ---|

Where d1:d5 e1:e5 f1:f5 and g1:g5 h1:h5 i1:i5 all have similar data.

Is there a way to have the data from def and ghi fall in line underneath abc with the condition being that there has to be something in the cell?

View 10 Replies View Related

Moving Vertical Data To Horizontal

Apr 20, 2012

I need to do is to move data which is in 3 columns with multiple entries (ID) in first column to single entry in first column (ID) horizontally.

Currently three columns are ID, Code and Reply:

111 A D
111 B F
222 A I
333 B D
333 C F

What I would need is (ID, Code1, Reply1, Code2, Reply2):

111 A D B F
222 A I
333 B D C F

Is there a macro I could use for it?

View 1 Replies View Related

SUMIF With Both Vertical And Horizontal Data

Jan 16, 2009

I need a solution for the equivalent of a SUMIF combining both vertical and horizontal data. The vertical cells align to the horizontal ones, but they're in a different table.

My attempted formula is: =SUMIF($H$22:$H$30,"TRUE",D7:L7)
*note that this is just an example set of data...my real data set is much larger (both rows and columns)

I need to be able to do this without transposing any of my data.

Things I've tried:
- Another option I tried was making D7:L7 a named range and using the transpose function (as an array) within the SUMIF formula above. I received an error.
- I tried using a bunch of IF statements added together (i.e. =IF(H22=TRUE,D7,0)+(H23=TRUE,E7,0)...); this actually works properly, but I get the "formula too long for cell" error when I put them all in (too many characters)

I'm using excel 2003 and windows XP professional.

View 9 Replies View Related

Vertical To Horizontal Data Extrapolation

Jan 12, 2010

How am I able to extrapolate data from vertical to columns?

I.e.

From:

ColumnA ColumnB

Field 1 Result 1
Field 2 Result 2


To:

Column A Column B Column C Column D
Row 1: Field 1 Result 1 Field 2 Result 2

This is different than Transpose.

View 10 Replies View Related

Converting Vertical Data To Horizontal Position

Jun 13, 2013

I need to transpose or move some of the data on Column B to horizontal position, is there a formula that I can use to do this? Attached is sample of what I want to do.

View 14 Replies View Related

Horizontal Autofill From Vertical Data Different Sheet

May 21, 2013

Here My first thread autofill horizontally from other sheet vertical data.

Vertical Data
Sheet1
A1
A2
A3
A4
& so on
AUTOFILL HORIZONTAL DATA
Sheet2
D3,D4,D5,D6..... fill by A1 A2 A3 A4.....

View 5 Replies View Related

Linking (referencing) Horizontal Row Data To Vertical Columns

Jun 28, 2014

I am working with two spread sheets; one is my take-off/summary sheet which is where I populate all the data for the project I am working on, the second sheet is also a summary sheet but it has also other functions; hence, I need to have two spread sheets.

I need to link/reference the data I have on my take-off sheet row 119, column K through ACL (lots of columns) to my other sheet but it now needs to be referenced in a column direction. At first, I enter = (sign) in T9 (the sheet I need to bring the data in) and then I go to the take-off sheet select N119 and hit enter, this first data is good, but then I try to drag it down to populate all the data from the other sheet but even though I lock the row (N$119) and let N free the formula doesn't populate this: =!N$119, =!O$119, ='!P$119 and so on so for...all what it gives me is =!N$119 all the way down...

View 13 Replies View Related

Transpose Data In Single Cell From Horizontal To Vertical

Oct 28, 2011

Currently we are transposing data in multiple cells from horizontal to vertical & vice versa.

But when i try to transpose data which are in single cells seperated with semicolon or comma, im not able to perform the action.

Is there any VBA function or public function to perform the this action?

Example:

From

A 1Dog; Lion; Parrot; Bee; Snail

To

A 7Dog8Lion9Parrot10Bee

11Snail

Like wise i will have to do the same action for the following

A B1Dog; Lion; Parrot; Bee; Snail2Goat; Crocodile; Love Birds; Bug; Snake3Hen; Elephant; Peocock; Mosquito4Dog12; Tiger78; Flies5Cat11; Bug1506Chicken7

View 5 Replies View Related

Call Horizontal Data Into A Vertical Layout With Additions

Apr 11, 2009

I am trying to reference data that is currently horizonal in excel and would like to reference that data to get output horizontally with some additions. I have pasted a screenshot of my test spreadsheet. how to do it step by step as I am completely a newbie in excel. As displayed below my data is in Cells A1 thru E3. I want to get output in Column H for this data but with text additions. For eg: in A1 I have Rachel 1/1 however in H1 that should become "My name is Rachel 1/1"....

View 9 Replies View Related

Find Data Based On Horizontal And Vertical Criteria

Mar 21, 2007

I have a spreadsheet that I am trying to create a formula for that will bring back the data found when you compare an X and Y axis. A sample is attached as the data is huge and I figured what ever you all created I could modify.

I need it to bring back the data found when I run my finger down the column till I hit the appropriate row.

View 9 Replies View Related

Converting Vertical Data To Horizontal For Varying Data - Large Amount

Jan 20, 2009

I have a one column spreadsheet. The column contains this data:

1 Name
2 Address
3 City
4 State
5 Zip
6 Telephone
7 Fax
8 URL
9
10
11 Name
12 Address
13 City
14 State
15 Zip
16 Phone
17 URL
18
19 Name
20 Address ... and so on

Where there may be one or two blank rows between the individual records and where there may or may not be a Fax number (or row) in the record.

I am trying to convert this data to a horizontal column format - which works fine if I do a copy/paste special/transpose. However I have to do this for 1,800 records and cannot figure out how to do this reliably.

I gave the above illustration to simplify but, actually this is a two column spreadsheet with individual row labels for every record using the above terminology. In other words the above text is in the first column and the data is in the second. Just thought I'd mention in case there was a way to do some kind of if/then formula.

View 9 Replies View Related

Convert Horizontal Data To Vertical - Loop Through Files In Folder

Apr 9, 2014

I have a set of workbooks-all located in the same folder-in which data are shown "horizontally"

i.e. file 1 - sheet1 looks more or less like this:

1 2 3
4 5 6

same with the other files.

what I am trying to achieve is that in another woorkbook, all the data are copied in one vertical vector

i.e

1
2
3
4
5
6 from file 1 and then
1
2
3
4
5
6 from file 2

I can seem to be able to resize the array to the correct size but the only values copied are for the last file. in the example that I have above, the result would look something like:

<empty>
<empty>
<empty>
<empty>
<empty>
<empty>
1
2
3
4
5
6

This is the code I have so far:

Sub Consolidate()

Dim a As Workbook
Dim b As Workbook
Dim x, y(), i&, j&, k&
Dim myPath As String
Dim filename As String
Dim wb As Workbook

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

View 11 Replies View Related

Macro That Transposes Vertical Data To Horizontal For Multiple Rows?

Aug 22, 2014

I'm having an issue transposing vertical data to horizontal rows. I know how to do the first set, but I would like to continue down the file for about 40,000 names....

Name: Steve
Address: 415 East Falls Ave
Zip: 19258
School 1: USC
School 2: None
Relocate? Yes

Name: Phillis
Address: 584 Fourth Ave
Zip: 19555
School 1: UCLA
School 2: NYU
Relocate? Yes

Same

Same

I would like it to look like this:

Name Address Zip School 1 School 2 Relocate?
Steve 415 East Falls 19258 USC None Yes
Phillis 584 Fourth Ave 19555 UCLA NYU Yes

View 5 Replies View Related

Convert Horizontal Data Into Cumulative (one-column) Vertical List

Feb 15, 2012

I have data (400 rows) that looks like this: (23 columns in total): (for this example I am using only 4 rows and 7 columns = 28 rows)

IN_0168 IN_0164 MH_0007 IN_0172 WCD3_03 WCD3_04 #N/A
IN_0176 IN_0172 WCD3_03 WCD3_04 #N/A #N/A #N/A
IN_0185 MH_0006 IN_0164 MH_0007 IN_0172 WCD3_03 WCD3_04
IN_0191 IN_0193 IN_0195 IN_0199 WAT10 #N/A #N/A

and I want it to look like this:

IN_0168
IN_0164
MH_0007
IN_0172
WCD3_03
WCD3_04
#N/A
IN_0176
IN_0172
WCD3_03

[code]....

This is what I was trying to do:

For m = 2 To 5
For n = 1 To 23
For b = 2 To 100
Sheets("Hierarchy").Select
Cells(m, n).Select
Selection.Copy
Sheets("HierN_H2V").Select
Cells(b, 1).Select

[code]....

View 2 Replies View Related

Macro To Link Horizontal To Vertical Data In Separate Sheet

Dec 3, 2013

I need to create a Macro that will link Horizontal data to vertical data in a separate sheet. Here's the Example:

I have data in a forecast spreadsheet that is horizontal, So, J2:U2 (12 columns for 12 months). This row of data needs to be linked in a single column of data on a separate sheet called "Price_Quantity". So I need C3:C14 (in price_quantity sheet) to be linked to the data in J2:U2 (in forecast sheet). Then C15:C26 needs to be linked to J3:U3, so on and so forth. 12 rows linking to 12 columns. All the way until C4959:C4970 is linked to J415:U415.

View 6 Replies View Related

How To Create Macro To Move Multiple Horizontal Data To Vertical

Jul 23, 2008

I need to create a macro to move variable multiple horizontal data to vertical format with certain infomation on horizontal will be duplicated following that variables. It's looks like below where you can see variables data in column F, G, H and I are moved vertically and at the same time column A, B, C, D and E will be duplicated following the variables allocation. I've tried to use transpose but it too manual and now looking suitable macro to help on this function

Original DataAccountDim 3Dim 4AmountCurrencyV20228V20242V20211V202044006003300BXXX 9.4USD0.591.923.343.554006003400BXXX 88.17USD5.5118.0331.3233.314006003500BXXX 7.27USD0.451.492.582.75Process to automateAccountDim 2Dim 3Dim 4AmountCurrency400600V202283300BXXX 0.59USD400600V202283300BXXX 1.92USD400600V202283300BXXX 3.34USD400600V202423300BXXX 3.55USD400600V202423400BXXX 5.51USD400600V202423400BXXX 18.03USD400600V202113400BXXX 31.32USD400600V202113400BXXX 33.31USD400600V202113500BXXX 0.45USD400600V202043500BXXX 1.49USD400600V202043500BXXX 2.58USD400600V202043500BXXX 2.75USD

View 9 Replies View Related

Converting Horizontal Data To Vertical Data With Multiple Rows?

Sep 8, 2009

reformat a table in which data for each named person is presented in one row with mutiple columns into a table in which each named person has multiple rows and one column of data. The solution to to this has eluded me so far.

The attached example shows before and after.

View 6 Replies View Related

Excel 2010 :: Value To Cell Based On Horizontal And Vertical Data On Matrix

Jul 30, 2013

I have chart like below. In empty cells I want either 1 or 0 (1 if software is installed and 0 if not).

Excel
Outlook
Powerpoint
Word

Computer1

Computer2

Computer3

Computer4

Computer5

Data of computers and their software are like this:

Computer1
Word

Computer1
Excel

Computer1
Powerpoint

Computer1
Outlook

Computer2
Outlook

Computer2
Excel

Computer3
Outlook

Computer4
Outlook

Computer4
Excel

Computer4
Word

Computer5
Outlook

So called Matrix Lookup was very close, but it finds data FROM Matrix (aka that first table). Is it possible at all?

Excel and Windows version:
Excel 2010 SP1
Windows 7

View 3 Replies View Related

Macro - Vertical Data Into Horizontal Data At Certain Interval?

May 28, 2012

I'm trying to rearrange vertically aligned data so that they are horizontal. But at the same time, I need this macro at an interval.

To give you an example,

Mr. Yang Xin 221-421-5123 Male College Grad
Ms. Taylor Cindy 534-123-5512 Female Uni Grad
In this case, we need the data to be arranged this way.
Mr. Yang Xin 221-421-5123 Male College Grad Ms. Taylor Cindy 534-123-5512 Female Uni Grad

Could you write me a macro?

View 5 Replies View Related







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