Horizontal Weekly Date Values Aggregated Into Single Vertical Monthly Value
Aug 27, 2013
I can't seem to figure out to easily aggregate & automate a series of weekly values (ie. forecast volumes by a specific part number) into vertical monthly summaries, by product.
Here's the before picture:
column A column B column C column D column E column F
Part num 1/1/2013 1/8/2013 1/15/2013 1/22/2013 1/29/2013A
100 100 100 100 100B
150 150 150 150 150C
200 200 200 200 200D
250 250 250 250 250
The output after manually doing this would look like this:
column A column B column CPart num Date QuantityA
Jan-2013 500B Jan-2013 750C Jan-2013 1000D Jan-2013 1250
View 5 Replies
ADVERTISEMENT
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
Nov 28, 2008
I have a problem that lookup vertical and horizontal corresponding values when there was duplicate values as it's only returning the first value found. What I want was to lookup the vertical and horizontal corresponding values on the left most & top most column based on the largest values column and also to return the duplicate values under the vertical and horizontal value column in ascending order if it's a duplicate values.
View 4 Replies
View Related
Oct 24, 2013
I have a data series running horizontally, which gives me monthly values e.g.
Jan 10 - Feb 12 - Mar 11 etc. Imagine the months are in row 2 beginning from column 2, and the values are in column 3.
I would like a formula which adds the three values, and in row 6 column two gives me the total for the quarter (10 + 12 + 11) and displays 33.
The next quarter value, I would like in row 6 column 3, etc.
View 5 Replies
View Related
Feb 20, 2013
I want to put vertical values to a selected space with horizontal orientation
See the excel file : FILL IN SELECTED DATA.xlsx
View 9 Replies
View Related
Mar 1, 2014
I have a spreadsheet with golfers handicaps, golfers names down the left in column A in rows 3-35. Row 2 has the event numbers titles 1 to 18 (18 events in a year). i need a graph that has the person as the vert axis and the horizontal axis needs to be the event numbers, so i can read down for the person and across to see how there handicaps change from each different event. i have attached the data below.
View 1 Replies
View Related
Mar 27, 2013
I cannot solve with Excel 2010 and I have searched all over for the answer.
I have sales data that is approximately daily and would like to count the monthly data and summarize it as an average for the month in a separate column.
For example, I would like to turn this:
3/2/2005 $xxxx
3/5/2005 $xxxx
3/20/2005 $xxxx
4/2/2005 $xxxx
4/10/2005 $xxxx
Into this:
March 2005- $xxxx (monthly average)
April 2005- $xxxx (monthly average)
I have a feeling some 'countif' formula would work but I am not sure how to do this.
View 4 Replies
View Related
May 14, 2012
I've got data in E4:I4, Monday - Friday.
What I need to do, is add the total of those 5 figures in a total, but then clear the data in those 5 days.
Then when I input the data for the next week in to those days, I want it to add to the previos week.
So basically;
In week 1 the 'Total' is just week 1
In week 2, the 'Total' is week 1 & week 2'
I will only be going up to week 5. (only week 5 once in a 3 month period, so 2 4 weeks and a 5 week)
View 1 Replies
View Related
Feb 25, 2014
I was wondering if it's possible to create a macro that would check the range horizontally and vertically and return the resoults in a userform so that when workbook is open this userform would come up with the information who is due for refresher....
The sample book doesn't show the real size of the original matrix which has 190 employess and about 30 procedures so as you can imagine it's a pain.. to keep it up to date so I need something to flag that for me, yes I can see the red cells but still there's so many cells that I might easly miss some of them....
View 14 Replies
View Related
Apr 2, 2014
I have data in a vertical column how can i equate that data to a horizontal row in other sheet. how to do it by formula?
View 4 Replies
View Related
Sep 15, 2009
What kind of formula do I need that looks for a date and find all the names that have the same character on that particular day
In the example below cell R4 kooks for date R3 in range A1:L15 and find all cells that have a "G" and displays all the corresponding names from C1:L1
I prefer non array-formulas
View 9 Replies
View Related
Feb 26, 2009
I assume there's a database (or pivot table?) solution for my task, which is to detect the days, weeks and months within a very long table, and obtain the totals of the data therein.
See attached worksheet.
View 8 Replies
View Related
Feb 19, 2012
I am wanting to calculate a rolling monthly average and a rolling weekly average.
The following cells have the headers k2 has Allan, Cell L2 has Bill, Cell M2 has Charlie, Cell N2 has Don, cell o2 has Ellen and Cell P2 has Flora
Column J3 to J14 respectivley has Jan to Dec
The balance of the cells will have the data.
I then need to plot the rolling averages for each person on a gaph as teh months data is filled.
Below is the table:
Monthly Totals 2012AllanBillCharlieDonEllenFloraJan0.0000.0000.0000.0000.0000.000
Feb0.0000.0000.0000.0000.0000.000Mar0.0000.0000.0000.0000.0000.000
Apr0.0000.0000.0000.0000.0000.000May0.0000.0000.0000.0000.0000.000
Jun0.0000.0000.0000.0000.0000.000Jul0.0000.0000.0000.0000.0000.000
Aug0.0000.0000.0000.0000.0000.000Sep0.0000.0000.0000.0000.0000.000
Oct0.0000.0000.0000.0000.0000.000Nov0.0000.0000.0000.0000.0000.000Dec0.0000.0000.0000.0000.0000.000
View 1 Replies
View Related
Sep 5, 2009
I am to the end of my wits - or maybe it's impossible to do the following with formulas?
I have the data like this:
Column A: Date (which is basically the date for the beginning of weeks)
Column B: Month of the date in Column A
Column C: Year of the data in Column A
Column D: Weekly data.
A: DateB: MonthC: YearD: Weekly data
12/11/200612200619
12/18/200612200644
12/25/200612200650
1/1/20071200741
1/8/20071200737
1/15/20071200741
1/22/20071200741
1/29/20071200741
2/5/20072200732
2/12/20072200736
2/19/20072200740
2/26/20072200735
Maybe it's because it's Friday night, but I just can't invent how to do the following:
Create a new column E that would contain the monthly sum of ColumnD across all weeks of this month - but entered only against the first week of that month (that is currently in Column A)
I.e., in my example it should be:
113
empty
empty
201
empty
empty
empty
empty..................
View 9 Replies
View Related
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
Jul 22, 2014
See attached for a clearer view : LOOKUP SEARCH.xlsx
View 10 Replies
View Related
Jan 9, 2014
I've been working on this for days, i still got no solution on what formulas I will put on cells in "To" tab. The excel file is composed of two tabs "To" and "From". We will convert the "From Tab" to "To" format. What formula will I put in D3 to V3 and drag down in "To" tab to convert datas from "From" tab.
View 9 Replies
View Related
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
Feb 2, 2014
I've got the following complex issue:
...................Trader1 Trader 2 Trader 3 Trader 4 Trader5... Trader20 (Horizontal)
Commodity1 .....0.....2.....0.....1.....1
Commodity2 .....0.....4.....0.....-3.....0
Commodity3 .....1.....0.....0.....5.....0
Commodity4 .....0.....0.....2.....0.....0
.
.
.
Commodity 36 .....2.....1.....1.....0.....0
I have 5 cells that are basically a drop down list of all the traders, so the user can select up to 5 traders.
Once the user selects up to 5 traders (lets say Trader 1, 3 and 5 for our example) I have the following requirements to fulfill:
Displaying all commodities that have non-zero, non-negative values associated with those traders in a list somewhere.
For our example, this would then display:
Commodity 3
Commodity 4
Commodity 1
View 4 Replies
View Related
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
Oct 29, 2009
I have a list with entries in one column like this:
sourceText
empty cell
targettext
sourceText2
category2
targettext 2
and I want to change it like this:
sourceTextempty cell target text
sourceText2category2target text 2
Attached sample data. Sheet one as it is. Sheet 2 as I want them to be (I don't need any of the formatting in Sheet 2).
View 3 Replies
View Related
May 23, 2012
I have a table where I need to return a value according to THREE conditions:
1. Vertical, which is a shop code for instance
2. And 2 horizontal:
- One: segment (heading level 1)
- Two: product (heading level 2)
E.g. for "Shop1", what's the value for segment "PI" and product "Credit", etc.
An example file attached : fz73m5zEmZS6Y14-C.xlsx
The key thing for me is to do this WITHOUT changing data structure, because that really would be a huge pain. I mean, if it's no possible to do without changes (e.g. adding an extra column, etc.), I'll have to live with that I guess. But ideally I would really like to avoid changing data structure.
View 8 Replies
View Related
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
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
Sep 4, 2013
I have a series of cash flows as follows:
Year 1 Year 2
Income 100 150
Expense 20 30
Net Income 80 120
I need to take the calculated "Net Income values" and also place them vertically by themselves, like this:
Year 1 80
Year 2 120
I know I can copy and paste the values easily using the "paste special... transpose", but how can I easily do this so that it continues to reference the original values? So that if I change the Income in year 1 of 100 to 120 the vertical Year 1 value would change from 80 to 100?
View 4 Replies
View Related
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
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
Sep 19, 2013
I am trying to create a formula that will sum a range based on both horizontal and vertical criteria using the following data:
1
2
1
20%
25%
2
40%
25%
3
40%
50%
For example, if the horizontal criteria were "1" and the vertical criteria were "2", I need to sum the 20% and 40% amounts (60%). If the horizontal criteria were "2", and the vertical criteria were "3", I would need to sum the 25%, 25%, 50% (100%). I have tried using SUMIFS functions, but they do not appear to work with both horizontal and vertical axis criteria (I receive a #VALUE error).
View 1 Replies
View Related
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
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
