Sum Of Columns If Values Of One Column Are The Same - For Large Spreadsheets
Apr 15, 2014
I'm in need of a formula that can give me the sum of cells in a column if the values in another column are the same. For example:
Col A
John Smith
John Smith
John Smith
Jane Doe
Jane Doe
Jane Doe
Col B
2
8
9
3
4
6
Col E
Monday
Tuesday
Sunday
Monday
Wednesday
Friday
The total for John Smith would be 19 and the total for Jane Doe would be 13. The problem is that this file is huge and we will have to use this formula on a weekly basis with different values each week, so it would be very difficult to use a "specific" formula for each person. Is this a possibility?
And to make it more difficult, I would then have to subtract one of those values if that row has a certain value in one of the other columns.
If values in Column A are equal to each other, sum of Column B. (Column C would contain the sum in this case.)
Column D = Column C minus B, if value of Column E is Sunday, otherwise don't subtract anything.
The total for John Smith in Column D would be 10 and Jane's total for D would still be 13.
View 10 Replies
ADVERTISEMENT
Apr 4, 2013
Is there a way to highlight the row and column of the active cell? The highlighting will move as the active cell is moved around the spread sheet. I have a newbie manager that is bugging the pea-turkey out of me! He wants to be able to easily see where he is within some of our sheets while he is in a vehicle or outside on a job site...seems like an "accessibility" type issue, but can't find anything in Excel "Options."
View 5 Replies
View Related
Jan 31, 2013
I have a large spreadsheet converted from pdf whose data still appears in A4 reading format.
I need to move part columns of data from 6 columns to form 1 large column in column A.
For example, move range B8 to B76 beneath range A8 to A76 and range C8 to C76 beneath that etc, page by page working through all 270 pages !
Also need to delete unnecessary 'page headers' throughout as in rows 2-6
View 2 Replies
View Related
Dec 18, 2013
I have a data that has several columns. But I need to separate the spread sheet based on one particular column values.
View 14 Replies
View Related
Mar 14, 2014
I'm running into an issue trying to calculate unique values in a Data column based on a few variables in other columns.
My current formula in Summary tab D4:D19 is
{=SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$H$1,
IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))
+
SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$I$1,
IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))}
This is currently counting the number of times a date value (data column I) appears for that name (A4:A19) in the data when meeting all of the conditions. I need it to instead count the number of times a unique date appears for that name with the additional conditions met (which all appear to work fine).
The results in the pink highlighted cells (Summary column D) should be:
Names starting with A - 3
All others - 2
I've left some other columns in the data with X's so that I can easily convert this back to my working spreadsheet.
View 2 Replies
View Related
May 15, 2014
I would like to align the matching values in columns A and B but I want the values that correspond to b in c,d and e to go with the column b value.
C D and E don't necessarily have a value in there but if it does it needs to move with B
View 5 Replies
View Related
Dec 20, 2012
I am using "FIND" method for comparing two columns in 2 spreadsheets. So if cell value in sheet1 matches with cell value in sheet2 then copy multiple columns from sheet2 to sheet1 (similar to what VLOOKUP does but I need to return multiple values if the comparison is true).
Values that I comparing are in Column A in both the sheets. Is match I need to return values from about 20 columns (from sheet2 to sheet1). One value that I am returning is in Column B, I was able to get that working. but other values are in columns starting from M. I do not know how to get those values from Sheet2 to Sheet1.
Following is the portion of code that deals with this FIND method
Code:
Set wsTools = Worksheets("AllTools")
Set wsToolParts = Worksheets("Tools and Parts")
Set ToolPartsRange = wsToolParts.Range("A2:A" & lR)
Set AllToolRange = wsTools.Range("A2:A" & lR3)
For Each ToolPartsCells In ToolPartsRange
[Code] .....
As you may notice that the following line brings next column (B) back but I do not know how to get other column values. They are in columns from M to Z
Code:
ToolPartsCells.Offset(, 1) = ToolCells.Offset(, 1)
View 4 Replies
View Related
Mar 27, 2009
I have two spreadsheets SP1 and SP2. I need to match values in column E of SP1 with values in column A of SP2. If I find a match in SP2, I need to find a value starting with 'TC_' from the previous rows and get the corresponding value in column B of SP2 and paste in column F of SP1.
For example, E1 value in SP1 matches with A3 value in SP2. So I need to find the row with value TC_AM01_axs_fhgn and get the corresponding value in column B AM01 and copy it in column F of SP1.
The two example spreadsheets are given below. The values in SP1 sheet has expected result in column F in red.
SP1
SeriesS.NoItem #CaseReuse CompScenario #A1abc001_CTC001AM01B2bcd002_ATC002AM02C3def003_BTC003AM03D4efg004_DTC004AM04E5ghi005_ETC005F6hij006_FTC006G7lmn007_GTC007H8opq008_HTC008I9rst009_ITC009J10uvw010_JTC010K11xyz011_KTC011
SP2
Case #SubjectTC_AM01_axs_fhgnAM01TC001asdhagsdjharteuTC_AM02_axs_fhgnAM02TC002asdhagsdjharteu12sdfsiu786dfgg88hdcfs676566532kjjjiTC_AM03_axs_fhgnAM03TC003asdhagsdjharteuTC_AM04_axs_fhgnAM04TC004asdhagsdjharteu
View 10 Replies
View Related
Oct 11, 2013
I have a list of domains (about 200,000) that looks like this:
I have a list of urls (about 1 million) that looks like this:
I want my results to only show this (since this is the only thing in the second file that does not contain anything from the first file):
I have tried programs designed to do this but they either (a) shorten everything down to the domain or (b) wont remove anything since the domains are part of a longer url in the second file.
View 3 Replies
View Related
Feb 22, 2007
I have two worksheets with products that I need to compare.
Each product has a code and a product name in the following format:
Column A has the product code and column D the product name,for
example:
ColumnA:
AM1BL15X
ColumnD:
AGLO MEL BLANCO 1C 15MM 215X244
Both sheets *should* have the same data in them but there are 4000
products that need to have their description verified.So I need to go
down each row on Sheet1 , extract the product code from
Sheet1.ColumnA and the product name from Sheet1.ColumnD. Then do a
search on Sheet2 for the product code (Sheet2.ColumnA) and verify that
the product description on sheet2 (Sheet2.ColumnD) is the same as in
Sheet1.ColumnD. If it matches,everything is OK. If not there is
something wrong. So I´m thinking that maybe the product codes that
dont have matching descriptions could be entered in to a separate
worksheet so that someone can check it later.
I´ve been reading around and found these code samples from this site
[url]:
Dim rng1 as Range, i as Long
Dim cell as Range
With worksheet("Sheet1")
set rng1 = .Range(.Cells(1,1),.Cells(1,1).End(xldown))
End With
i = 0
for each cell in rng1
if cell.Value worksheets("Sheet2") .Range("A1").Offset(i,0).Value
Then
' do what - they don't match
else
' do what - they match
End if
i = i + 1
Next
I´ve been trying to make some changes but I dont quite know how to get this working to do what I want and I only get errors.
View 9 Replies
View Related
Feb 22, 2007
I have two worksheets with products that I need to compare. Each product has a code and a product name in the following format: Column A has the product code and column D the product name,for
example:
ColumnA :
AM1BL15X
ColumnD:
AGLO MEL BLANCO 1C 15MM 215X244
Both sheets *should* have the same data in them but there are 4000 products that need to have their description verified.So I need to go down each row on Sheet1 , extract the product code from Sheet1.ColumnA and the product name from Sheet1.ColumnD. Then do a search on Sheet2 for the product code (Sheet2.ColumnA) and verify that the product description on sheet2 (Sheet2.ColumnD) is the same as in Sheet1.ColumnD. If it matches,everything is OK. If not there is something wrong. So I´m thinking that maybe the product codes that dont have matching descriptions could be entered in to a separate worksheet so that someone can check it later.
I´ve been reading around and found these code samples from this site ...
View 2 Replies
View Related
Apr 20, 2014
I have 3 workbooks "ID numbers, 07 Car,and 08 Car".
I need a VBA code that will use the value in column C in each workbook, look up the value in workbook "ID numbers", column C thru G, and return
values into each workbook:
Workbook ID number Column C add values to Each workbook (Column B)
Workbook ID number Columns D thru E) add values to Each workbook (Columns D thru E)
See 07 Car workbook highlighted in orange, every product below the orange highlight must look like for both workbooks this after the code is ran.
View 5 Replies
View Related
Jul 9, 2014
I have successfully populated a website forms using Excel VBA. As a continuation of this task, i need to calculate the total of a column based on two other columns in the same worksheet.
Column A contains Date, column B contains 3 fixed alphabets (I, L and K) and column C contains time. I have attached a sample sheet here. sample.xls
How can i write a VBA code to find the sum of effort values of column C for each values in column B for a given date (value in column A).
View 9 Replies
View Related
Mar 20, 2014
I have a two different workbooks book 1 and book 2 with some data.
If values of first two columns ( Column A and B) of both workbooks are matching then I want a formula that can return the value of third column (column c) of book 2 in book 1 column c.
example:
Book 1
column A / Column B / Column C
100 / 200 /
Book 2
Column A / Column B / Column C
100 / 200 / 300
now I want a formula that can return in column C of book 1 to display 300.
And the data runs across some 100 rows in both workbooks.
View 4 Replies
View Related
Jan 8, 2009
I have 2 columns and the formatted values sholud look like in the 3rd column, how to do this? see the Excel attachment. 2nd column has different length of characters, but in the 3rd column when it is formatted it should be 7 characters only.
View 3 Replies
View Related
Jan 28, 2010
=(SUMIF($J:$J, "Player1",$K:$K)+SUMIF($L:$L,"Player1",$M:$M)+SUMIF($N:$N,"Player1",$O:$O))-SUM($I:$I)
This looks up if Player 1 was in the top 3 scoring zones and adds up the players total score.
I have another column in this table of data labelled 'Number of Players'. I was wondering how to lookup the total score for this player in the games he played under 6 players for example and the games he played in where there were other six players.
I've tried using that formula ^^ with variations of IF and SUMIF formulas to try and figure it out, but I keep getting formula errors or it just returns with the total score, and not just with the total score under 6 players.
I am also trying to create a graph of date on the x axis against score to date on the y axis - everytime I try, the x axis is always numbers instead of the date?
View 8 Replies
View Related
Mar 14, 2014
I have three rows of data (29-31) from columns D to Q.
I would like to take D29 and add each of the values in the adjacent columns exactly once and sum up the value D thru Q
For example I'd like to return not just Sum(D29:Q29) but also Sum(D29:R29)+Q30, Sum (D29:R29) +Q31.
Macro that would add these values?
That is D29 + E29 or E 30 or E31 + F 29 or F 30 or F 31 + G 29 or G 30 or G 31... and on?
Even though I wrote "or" above, i would need the other values to be added in a separate calculation, until all possible permutations of D and the following 8 columns and 3 rows of data are exhausted.
View 1 Replies
View Related
Mar 24, 2014
getting a vba formula to copy values from two columns and paste it in 3rd column on the click of a button.
For example:
if I have 3 rows filled in column A and 5 rows filled in column B then i need a formula to copy 3 rows in A and 5 rows in B and paste it to column C. so column C will have 8 rows now.
The number of rows the value can be entered can vary. [ example: sometimes we may have 6 rows filled in A 2 rows in B ]
View 9 Replies
View Related
Dec 13, 2013
I have a challenge related to a excel sheet we are using for personnel planning.
Vertically we have several projects with action items listed row by row in the following format:
- Project 1 xxxxxx
Project management
Project Engineering
Workshop
Shipping
Etc.
- Project 2 xxxxxx
Project management
Project Engineering
Workshop
Shipping
Etc.
Horisontaly on the columns we have dates, day by day for a year +
For each day we chose from a drop down who is assigned to the action item. The problem is when there are 30-40 + projects and each have 20-30 lines of ation items it is hard to get the overview and avoid double entry. I would use conditional formating to do this, but firstly each project is separated by a line containing different formulas and I would not like the conditional formation to change these cells, and at the same time to put this up manually for each row would take a long time since there are 365 + rows.
how a VBA code could solve this little challenge? IT would also be beneficial to have it do the check upon cell change, that way we would not need to manually run the VBA to check.
View 4 Replies
View Related
Oct 6, 2009
My Data
Service 1 | 100 | 1.5$
Service 1 | 200| 2.5$
Service 1 | 300| 3.5$
Service 2 | 100| 11.5$
Service 2 | 125| 21.5$
Service 2 | 300| 30$
I need to extract the value in the third column according to an exact match of the first column (the service name) and a range match of the third column.
I tried SumProduct but the problem with my formula was:
Let's assum I ask for
Service 1, 200
The formula summed up 1.5 and 3.5 and returned 5
When I wanted to return one value from this table.
View 9 Replies
View Related
Apr 28, 2007
I have three columns as follows:
ItemsWeekEnding Count
a14/7/2007 10
a34/7/2007 3
a14/14/2007 11
a14/7/2007 5
a44/7/2007 6
a14/21/20078
a44/14/20072
a34/21/20071
a24/7/2007 6
a14/28/20077
a24/21/20074
a94/28/20079
I want to Sum Count values (column 3) for matching WeekEnding (Column 2) and Items (Column 1), example:
a1 4/7/2007 15
as a1 for this weekend is occuring twice.
I need the formula to do it in excel.
View 9 Replies
View Related
Jan 30, 2008
I have 2 spreadsheets, I need to collect the data onto 'figures 07' from column D on 'spreadsheet 07' by date, based on the name matching. ie cell B4 on 'figures 07' should be 166 and cell C4 should be 72. I've tried IF and LOOKUP but they don't give me the correct answer Auto Merged Post;Here is the second spreadsheet, it wouldn't let me attach both of them on my first message
View 4 Replies
View Related
Oct 3, 2008
I have a large spreadsheet with over 50 columns. Many colleagues use the sheet and often use the format, hide, column function to hide columns they do not need but then the next user has to unhide them and re-hide others until they are left with the different columns they require.
Is there anyway of creating a menu which allows users to select from a list of all the columns those they wish to display, all the others therefore being hidden.
View 5 Replies
View Related
Nov 18, 2008
In column A I have a list of product names..(e.g....ABCD, JKLM....)...this is the "master product list" -- 1000 rows of products.
In columns C:G, I have additional product names, each column represents products sold by state. (each column lists between 100-250 products).
I want to do a VLOOKUP and I wanted to know what products that are in
A1:A1000, that is NOT listed in C:G?
My experience with VLOOKUP is limited...but I do know that I can only look in 1 column at a time.
How can I write it to look at the ENTIRE range? I suppose I can copy all the all the products to 1 column...?
View 6 Replies
View Related
Oct 3, 2008
I have a large spreadsheet with over 50 columns but usually only need to view a small selection of them. Many colleagues use the sheet and often use the format, hide, column function to hide columns they do not need but then the next user has to unhide them and re-hide others until they are left with the different columns they require.
Is there anyway of creating a menu which allows users to select from a list of all the columns those they wish to display, all the others therefore being hidden.
View 9 Replies
View Related
Feb 14, 2013
In the attached, sheet 2 has a formula that pulls the Item (column B) from Sheet 1 based on Instocks (column F) being less than the value in N2. Would it be possible to add a second criteria to the formula in sheet 2? In short, can I pull the item from column B of sheet 1 into sheet 2, where in sheet 1, column F values are less than N2 AND where column G values are greater than O2?
I'd love to just vlookup the fill rate value and then filter it down, I know at some point I will be asked to weed it down a bit via a formula instead.
View 1 Replies
View Related
Sep 30, 2006
I got 3 columns of cell values as follows:
Demo 1
COL A COL B COL C COL D
_ 12 14 16
_ 32 12 18
_ EA LW RA
Demo 2
COL A COL B COL C COL D
12 12 14 16
32 32 12 18
EA EA LW RA
14
12
LW
16
18
RA
Can data of the 3 columns be copies (by vba code) to the blank col A in the exact sequence as shown in Demo 2?
View 2 Replies
View Related
Aug 20, 2014
Say I wanted to concatenate a row that had 10,000 values in it. How could this be achieved without having to click 10,000 cells while typing a comma in between each one?
View 5 Replies
View Related
May 15, 2007
I have a large order spreadsheet with 3 date related columns. One for customer requested delivery date, one for factory quoted delivery date and one for actual delivery date.
I want to be able to set something up that if it gets near either the requested or quoted delivery date (by say a week), that the cell flags red so it's a visual reminder to check whether the order has been dispatched etc.
View 9 Replies
View Related
Jun 28, 2013
I have VBA code to perform some actions on data in excel file and then convert all that data into semi-colon separated CSV/text file (code below).
Now, all I want is to add VBA code in the existing macro to find a column header (say, "Application date") and then convert all the dates into YYYY-MM-DD format. The original values in this column don't have a fixed date format.
Code:
Public Sub ExportToCsvFile(FName As String, _
Sep As String, SelectionOnly As Boolean, _
AppendDataOnExistingFile As Boolean)
Dim WholeLine As String Dim FNum As Integer
Dim RowNdx As Long Dim ColNdx As Integer
[code]....
View 3 Replies
View Related
