I am attaching a worksheet which shows what I would like to see. I am having difficulty writing the formula. The database I have is a list of forex trades. Each trade is made at a specific time, and involves a specific currency pair (such as EURUSD). I want to summarize the trades in a table which sums up a week's trades for each pair. (see worksheet).
View 5 RepliesI want column J to reflect the total amount of all trades which were open at the time the trade on the row is closed.
(the data is sorted according to closed trade times).
So while the trade on row 4 is the first trade to close, there were other trades also open prior to the closing. I need to show the total amount in column E of all trades which were open during the time window that the trade on row 4 was open.
I have raw data with date & days of the month .
From this raw data I want to get the collective sums for each day of the week using Pivot or any other means - I tried Pivot but propabaly due to lack of my understanding of the same the desired result was not recived
I have attached a spreadsheet of an example of a database with a pivot table and a desired report
Can anyone give me a pivot tabe from my database that looks like my desired report.
I am willing to seperate the database into two seperate databases with different transaction types if necessary.
I'm trying to sort a table as displayed in the image below. The table consisting of items in column A, here fruits, and a column E where totals over three month is displayed.
I have entered an array formula in column H where the total of each fruit is summed and then sorted in descending order. My problem is that whenever two fruits have equal value (here, apple and pear), Excel will not manage the display of both fruits, it will just display the first twice. The formula in column I is a simple SUMIFS.
It doesn't matter if Apple or Pear is displayed first, but I need both items to be displayed in column H.
excel1.png
I am using Excel 2003.I need two distinct sums:
I need to calculate out the loan amount of approved loans by week (weekending on Sunday). For example for the week ending December 28th, 2013
$1,234,567,890 in total loans
I also need to have a list of sums of the loan amounts per code types of approved loans. For the week ending December 28th, 2013
$123,456.00 in total loans for Code: LTV/TLTV > Guideline max
$987,654.00 in total loans for Code: FICO > 10 pts below Guideline min
I need to ignore any loan amounts and codes for any loans that have not yet had a approved date entered and also ignore all loan amounts for any loans that are denied. I have tried doing this in many different ways with Pivot Tables and I am not getting the results I need.
I have a master worksheet that contains a lot of datas. Example below:
Invoice # Code Customer Money
1 1101 a 10
2 1102 a 20
3 1102 b 30
4 1103 c 40
5 1104 d 50
6 1101 a 60
7 1103 c 70
After creating a pivot table with code, customer as Row and money as Data, I get this:
Sum of money
Code Customer Total
1101 a 70
a Total 70
1102 a 20
a Total 20
b 30
b Total 30
1103 c 110
c Total 110
1104 d 50
d Total 50
Grand Total 150
Suppose now, I have another data named Rate that I need to associate with each pair of Code and Customer. See below:
Sum of money
Code Customer Total Rate..................
I use INDEX and MATCH equations quite frequently now and they are invaluable! However what I'm trying to do now doesn't quite fit. I've attached a small sample of my workbook. The sheet called 'data' has three columns of data that I need to split into a grid (called summary). I'd know how to do the opposite of this, as in changing from a grid to a list by using an index and match. But I'm not sure how to do this the opposite way around?
View 3 Replies View RelatedI have an Excel sheet which has Category Column, I have set an auto filter for it, When I click a particular Category data pertaining to that Category is visible.
But my problem is I need help (VBA), that loops category autofilter, through each Category item and capture( sum number of items in that particular category) and put it in an other excel sheet. Kindly Help.
This data consist of a long list of staff id with exception type and points. However, I have only a certain number of staff from the long list which is applicable to my department. For example, I only need data for 4 staff from the list. This list will duplicate the staff id based on dates. Therefore, there might be several cells with exception type and points for that particulat staff . Example is for staff M41 as below:
Date Staff ID Exceptiontype Points Item#
Dec 1 M45 Contract 1.0 44556
Dec 1 M41 Contract 1.0 44578
Dec 2 M43 Contract 1.0 44579
Dec 3 M46 Contract 1.0 22569
Dec 4 M41 Contract 1.0 44572
Based on the above data, I need to summarize as below:
Staff ID Exception Type Points Exception #
M41 Contract 1.0 44578
M41 Contract 1.0 44572
I am looking for formulation/organizational advice for a database of over 3000 MLB Baseball games. This database would break down each game based on 7 different factors that are evident in some games and abscent in others...
What I am looking to do is to create a formula that resembles a "choose your own adventure" sort of scenario...For example, If i wanted to immediatly view all the games in the database that exhibited 1 factor, say "Games where home team won by 5 or more," then I could easily view all of those games... Then, it would have the capability to go deeper, say now I only wanted to view "Games where home team won by 5 or more and away team got 6 or less hits"...Etc...
I have a worksheet with several columns of data that consists of month to date billing, cost, profit, etc. that are tied to job numbers.
I currently have code ( AdvancedFilter) that seperates this data judging by the first two numbers of the job number. For instance it will take all job numbers that start with 11, copy them to a worksheet, print it and then do the same for 12, 13, 21, etc..
I need a formula for the highlighted yellow area that pulls and sums from a reference table. Not a traditional Vlookup because the reference within the report can appear in multiple columns within multiple rows.
ReportABCDEF1AccountJanFebMarApr2Overtime(L7)Overtime300 300 300 300 3
Salaries(L8)Salaries1,170 1,170 1,170 1,170
Expected ResultsReference PageABCDEF1
Account 1Account 2JanFebMarApr2
Additional PayOvertime(L7)1251251251253
Additional PayOvertime(L7)1751751751754
Salaries(L8)Ex Staff2252252252255Salaries(L8)
Ex Staff3253253253256Salaries(L8)Ex Staff4104104104107Salaries(L8)
NE Saff1101101101108Salaries(L8)NE Saff100100100100
summarize quarterly data in the summary table taking into consideration the months and the district. see the attachment for a more clear picture.
View 4 Replies View RelatedI have three lists and I want to summarize them in one table. I can do this by formula or using Data validation by position. Who is the best way? There is a difference between them?
View 3 Replies View RelatedMacro or formula to build a table from a data.
The data is in the following format in 4 columns: A (Customers' names), B(type of transactions: invoice,payment, Credit,Check), C (date), D(Amount).
Data is for the whole year by monthly.
I would like to build a table like this:
Column (A) 10 names of customers and in next 12 columns by month amounts and types of transactions.
I am working on a pivot table just like the one on the picture here [URL] .......
Ideally I wanted the sums by date to be displayed horizontally and not vertically as shown in the picture, but was unable to do so. Anyways, I want to be able to filter those dates, so I can display data just from an specific date, and then change this date whenever I want and the new data will show up.
I tried doing by the checkbox that appears under the "data" dropdown on top of the column; However, when I uncheck a specific date, it dissapears and I have to add it again if I want to see it. Is there a way to keep the values on the dropdown even after I uncheck them? Or even another way to filter the information? Below is the pic of the dropdown I get when trying to sort the data, But as I said If I uncheck one of the values, it dissapears from the list.
[URL] ........
I have several data sets that that have the same column headers (product portfolio data, split by categories into different workbooks) and the same structure and want to merge them to create summaries in a pivot table that I can slice the way I need to look at the data (e.g., by "Category 1" level, "Category 2" level, etc..). The messy way would be to just copy/paste all data in one master set and then create one pivot from it. The problem is that this a) takes a lot of time since I have several data sets and b) the content of these data sets is constantly changing, so I would need to constantly update the master data set by replacing updated sub sets.
I was hoping that I can use PowerPivot for that which is apparently not the case - as I understand PowerPivot is only being used to link data sets that contain a column with an identical key to then basically do on a larger scale what a vlookup would do. Is there a way to do this with PowerPivot?
I have several competitors balance sheets (around 15), they all have the same structure, what i will like to do is summarize all this data into a pivot table to have a grand total but also be able to filter the data by single competitor.
I have tried to do multiple consolidation ranges, power pivot, pivot but i was unsuccessful, maybe i arranged the data wrong or im not using the right solution. im using excel 2013
I want to accomplish something like this but slightly different:
[url]
I have the same issue but a bit more complex. In my case I have an additional amount of columns (let's say dimensions). So for the example given (sales per month) I would add two columns for 4 different regions and 5 machine types (just examples).
The output (that would serve as pivot tabel input) would then have to be a 4 x 5 x 12 x 4 = 960 row database table.
I have tried to accomplish it by customizing the given code but that provedto be beyond my powers ;o)
and some 3rd party software [url]and [url] but non of them is doing what I'm looking for.
The code I'm looking for ideally would count the number of columns and unique records per column and construct the database table from that. So I am looking for flexibility in the number of dimensions also...
I need to calculate and compare the extra number of hours worked less lunch time (0.5h per day) "per day sum by week" and total "per week" :
- number of hours above 40h per week;
- number of hours above 9h per day sum by week;
and keep the higher.
I've nearly found the formula to have extra hours for total week
=SUMPRODUCT(($B$3:$AK$3),--ISNUMBER($B$3:$AK$3),--(WEEKNUM($B$2:$AK$2+0,2)=AN1))-(SUMPRODUCT(--(WEEKNUM($B$2:$AK$2+0,2)=AN1),--ISNUMBER($B$3:$AK$3))*0.5)-40
But for total week I don't know how to sum per week hours above 9+0,5 per day
test extra hour.xlsx
This line will give me the current weeknumber
wkNumber = DatePart("ww", Now(), vbMonday, vbFirstFourDays)
But also i would like to have a dynamic array that will give me the dates of this week
Currently i do that using vlookup in a table stored in my personal.xls but that is no longer possible as i have to distribute my macro and i no longer have that possibility.
All I am doing is counting text values in a table.
The table has 3 main columns(which are relevant to this thread anyway).
Shift - Area - Status
The example I have attached shows examples of DCOUNTA, SUMPRODUCT and a Pivot Table.
I have read many threads stating that the best one to use is Pivot Table followed by DCOUNTA followed by SUMPRODUCT.
The most effective for me seems to be SUMPRODUCT (although this does slow excel down dramatically when you use a lot of these formulas). As do Array Formulas
The Pivot Table does not update on its own, therefore constantly needs to be refreshed. (I could use code to do this)
The DCOUNTA seems to be the least effective at doing what I want (unless I am doing something wrong)
In the attached example can the DCOUNTA be used more efficiently as I don't like the fact that I am duplicating rows to apply the criteria for a different shift. e.g
Area 1 - Late Shift - Banned
Area 1 - Early Shift - Banned
I want my table to be as follows (as the SUMPRODUCT shows)
AREA - Early Shift - Late Shift - Night Shift - Area Total
Area 1
Area 2
Area 3
Area 4
Area 5
Shift Total
I have an array that is 10 rows by 4 columns. I would like to sort the whole array based on sorting column 1 in descending order. Any good ideas to edit or write new code? Anything to send me in the right direction would be sweet as well!
Im working on a project where a spreadsheet holds data which is spread over multiple rows. The number of rows can change depending on the case.
Imagine 1 case being 1 row of certain details, then a various number of rows below detailing some different information in different columns.
A crude example is this: ...
I'm trying to sort an array which is of variable height/rows when put on a worksheet. I need to sort by column C ascending (smallest to largest) and then by column D ascending (oldest to newest). I tried recording a macro but the code is just junk.
Code:
Dim BookSPSortingWorksheet As Worksheet
Dim TR As Range
Set BookSPSortingWorksheet = ThisWorkbook.Worksheets.Add
Set TR = BookSPSortingWorksheet.Range("A1").Resize(UBound(array_book_SPNames), UBound(array_book_SPNames, 2))
TR = array_book_SPNames
Is there a way I can use the sort function like the below by indexing my TR range for columns 3 and 4?
Code:
TR.Sort key1:=TR, order1:=xlAscending, MatchCase:=False
Have an array that contains file names similar to these:
\serversharesmysharesomefolder3_s6_macro_cycle22_2Ft10p75.txt
\serversharesmysharesomefolder1_s6_macro_cycle22_2Ft10p75.txt
\serversharesmysharesomefolder25_s6_macro_cycle22_2Ft10p75.txt
\serversharesmysharesomefolder32_s6_macro_cycle22_2Ft10p75.txt
I need to sort the array in ascending order based on the file name. The file name is after the last “” and starts with a number(s). From my example above I would like the resulting sort order to look like this:
\serversharesmysharesomefolder1_s6_macro_cycle22_2Ft10p75.txt
\serversharesmysharesomefolder3_s6_macro_cycle22_2Ft10p75.txt
\serversharesmysharesomefolder25_s6_macro_cycle22_2Ft10p75.txt
\serversharesmysharesomefolder32_s6_macro_cycle22_2Ft10p75.txt
I want to use the Application.GetOpenFilename to populate the array and then process the filenames.
I assumed you data starts in "A1"
Results start "F1"
Code:
Sub MG19Dec38()
Dim varFiles As Variant ' used to hold array of file to open
Dim ray
Dim j As Long, i As Long
Dim Temp1 As Integer
Dim Temp2 As Integer
Dim Tem As String
' ray = Application.GetOpenFilename("Txt Files (*.txt), *.txt", , , , True)
I'm opening a non-excel file in excel and it formats it into a series of rows/columns. The problem I have is that it doesn't consolidate all related data in the same row, for each new column of data it breaks into a separate row. I'd like to easily roll it all up into a single row.
I've attached a sample file to illustrate. The "Original" sheet is what I'm starting with and the "Final" sheet is what I hope to condense the data into.
I have a pretty vast database which I populated through VBA from a certain number of worksheets. I now need to create a table which shows min, max and average for each location given x, y or z condition. So far I only approached it by creating a secondary table with each range address and then using INDIRECT, but this would require extra work on the database (insert a function to sort out locations alphabetically and within each Range.Location sort out by conditions (note. A, B and C are not in alphabetic order)) and is also very ugly.
View 3 Replies View RelatedI have a spreadsheet with 15,000 rows x 15 more columns, i would like to find out the number of delivery for each single order.
The database similar as below :-
Order No. Delivery Date Product
12345678 01.01.2009 TV
12345678 01.01.2009 DVD PLAYER
12344568 02.01.2009 TV
14567892 03.01.2009 RADIO
14567892 04.01.2009 RADIO
14567892 31.12.2008 BLUE RAY
15465879 06.01.2009 MP3
1)what excel function should I use in order to find out the number of delivery since there are so many duplicate records in the database.
2) Shall I use "Pivot table" ?