I would like to take the data in the first table and put it into the format of the data in the seconde table "Using EXCEL Functions" i.e. Not VBA code/MACRO. Note there are multiple occurances of some names in the first table with different fruits.
Name Fruit
MarkOranges
SteveApples
RoyPears
TedTomatoes
JohnApples
JohnPears
SteveTomatoes
RoyOranges
AlbertOranges
MarkPears
RoscoeTomatoes
OK I have included the spreadsheet I am working with and I will try really hard to explain exactly what i want and where my problems lie, so please bear with me.
The work book is separated by months. The individual spreadsheets can have multiple entries in each cell, I have a code running to automatically pop up a comment text box for each entry into that cell to separate each occurrence.
Now I have a separate sheet that will be showing how many times each item has a "hit" or occurrence for the entire month. That's where I have the problem. I don't want to go and count every time i made an entry, excel should do that. The problem I am coming up with is that I can only get it to calculate the one occurrence per cell which does me no good if there were three separate occurrences in that cell.
Is it not possible to use entire columns references when you're looking up one value and wanting it to return multiple corresponding values? For example, my formula seems to work fine when I use something like:
=INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$C$10,ROW($A$1:$A$7)),ROW(2:2)),2)
(with an array)
But if I change $A$1:$B$7 to simply $A:$B (and the rest to straight column references) it gives me a "NUM! error. I need my range to reference a dynamic range because values will be added to the table.
Basically, I need a formula that will return multiple occurances of the same lookup value.
I need to produce a summary of the monthly spend with parts suppliers for a number of vehicles, with each vehicle having it's own worksheet. The suppliers' names are in column C with the costs in column H.
So what I need to do is sum the results of column H on each worksheet when the supplier's name (which is on the same row) matches "Triple 7" and it's in the same month. The idea is to produce a sheet which displays the total spend each month with our suppliers.
Is there a way to find the minimum value in column B that corresponds to the two occurances of "Yes" in column A.
Column A Column B
NO 1
NO 4
NO 7
YES 6
NO 3
NO 9
Yes 2
[Note: the numbers are in column B.]
I basically want to return the # 3 from Column B.
How can I count the number of cells in a range which contain a specific string?
I'm trying to use SUMPRODUCT to no avail:
=SUMPRODUCT(--SEARCH("RSC",I3:I3000)>0)
I'd like to count the number of time Y or Yes appears in a column. In a different cell I would like assistance in figuring out what formula could calculate the percentage of attendance based on the total Y's or Yes's.
View 3 Replies View RelatedI am trying to use an SUM array to count occurances of a given criteria.
Here is an example of one that works for me.
How can I add the number of remarks to the number classes based on their row?
I have a TALLY SHEET which auto computes the number of occurances of each classes
and remarks…can someone help me how to add the class and remarks? In this example
you can see that CLASS A occurred 3x ,YES remarks occurred 2x and NO remarks occurred 1x for A class…
how can I add the occurances of YES and NO remarks to A class?
This should be the output…Remarks are being added according to the class they belong
ABEYESNONot Applicable
643242
I am trying to find the most occurances in one column of the same word (List of Suppliers) and return how many times that name cropped up, would like to use a single formula to do so and not have another column with numbers in it if possible.
View 9 Replies View RelatedI have a column that has almost 500 rows. Each cell has " DELETED", "OPEN" or "CLOSED" written in the cell. I need to know how to calculate how many times "OPEN" is listed throughout the column.
View 4 Replies View RelatedI have the following (same fields) data in multiple sheets (named P1, P2 etc).
Would like to get the result as a table, based on the sheet name and the cell reference in that sheet.
P1 (Sheet Name)
row A B C
1
2
3
4 Data1 x
5 Data2 y
6 Data3 z
P2 (Sheet Name)
row A B C
1
2
3
4 Data1 a
5 Data2 b
6 Data3 c
New Sheet (Intended format of the result, based on the A3, A4 and B1, B2, B3 values)
row A B C D
1 Field Position B4 B5 B6
2 Sheet Name Data1 Data2 Data3
3 P1 x y z
4 P2 a b c
I am trying to populate a large data table with data sourced from multiple tabs.
Each of the tabs is, for the most part uniform.
They have column labels of "Invoice Number", "Schedule Dates," and "Amount". Their cell references are A6, B6, and C6, respectively.
Is there any way to congregate this data into one massive data table? It doesn't matter the order of the data table. I will be using sort/sumifs/pivot tables to analyze the data.
I cannot copy and paste as there is too much data that changes on a daily basis.
I am working with a large table of quite simple data in the following format.
Ref1 SurnameInitialsDOL1 Reference2
Zz719147GLAWSJ07/04/19780271947X
YA414253CDORCE07/04/19870282179F
I need to split up the "master" table so that for all rows where DOL1 falls with a year range of e.g 6th April - 5th April (in any year) then this row is moved or copied into a new tab based on the year range.
i.e. The first row should appear in a new tab for the range 6th April 1978 - 5th April 1979.
The marco should be ale to create and name new tabs. The tab name will be 78_79.
The second row should appear in the 87/88 tab.
I have several thousand rows of data with dates ranging from 1978 - 2012 so there should be tabs populated for every year from 78-2012.
I am attempting to populate multiple rows in one column with data from another table. I need to get the correct street address using multiple values, i.e. first name, last name and city, as some of the names double up.
Is there a way to do this? I have pasted below an example of what I need done as reference.
Last Name
First Name
Address
[Code]....
I have a data table that has the following structure
Team manager Warrington 10 12 14 16
Team manager Liverpool 8 10 11 12
Commercial Manager Warrington 25 28 32 33
I need to extract the data out into a flexbible summary table where i can choose the job title, location and year.
I can use the following index / match formula to extract the salary that matches the job title and location.
(INDEX('Emp In'!$c$5:$c$130,MATCH(1,('Emp In'!$A$5:$A$130=$B3)*('Emp In'!$b$5:$b$130=$C3),0) - Where b3 = job title, C3 = location, Column C = Salaries year 1
However I cannot add the flexibility to choose a year as this formula fixes the index on a chosen column (yr 1 column C is this instance)
I have a workbook that contains 52 spreadsheets (one for each week of the year). Each contain the same column headings. The columns contain both numberic and text data which I need to pivot. I can individually pivot each sheet to obtain weekly data but I would like to obtain year to date data throughout the year. How can I merge all of my 52 sheets together to utilize just one pivot table.
View 6 Replies View RelatedMy pivot table wants to total 2 sets of data and put it in rows. I would like it to be in columns.
The small attached sample shows what I would like.
Want to be able to select data from Column C (3 data validations/drop down menus) and have the corresponding values display in the appropriate fields in column H. Not sure if this is possible?
View 4 Replies View Relatedi am attaching a sheet here.
i have put data validation list in three columns
from data lsts i select unit type , then hinge type then code.... the problem is that as soon as i select code all the values (H W D ) in the next three cells should automatically change.
the values against each cabinet code are also provided in the same sheet.
Sr No.
Unit Type
Unit Type
Code
H
W
D
Qty
1
Base Unit
Double Hinged Door
B60
720
600
580
2
2
Base Unit
Single Hinged Door
B30R
720
300
580
4
I am trying to generate several pivot tables from one data source table. I have successfully created my first pivot table (A date field, and a water storage facility level reading) and subsequently a graph from this. I have worked out that I need to group my dates as I am supplied a daily reading, but only need monthly average. All worked great.
Now i need to create more pivot tables and graphs. The next one I want is to create one grouped by years. But when I create this new pivot table and change the grouping of the date field to yearly, it also changes the grouping on my first pivot table, which is undoing my work.How to tell excel that these pivots are independent, and I don't want them changing in unison? See screen grab of my source data and where I am up to...
Microsoft Excel - 401027_0100.00_0221.00.csv_2014-03-10_11-41-35.jpg
I have a workbook that contains something like 50 worksheets and they all contain data with the same columns, for example column A is Project, column B is Project Name, etc. I need to convert data in each worksheet into an Excel Table. There has to be an easier way than manually converting each worksheet into a Table. However, when I group all of the sheets, the option to Insert a Table is not available even though the data starts in the same row and contains the same number of active columns in each worksheet. Is there an easier waty to insert a table in all of the worksheet simultaneously?
View 2 Replies View RelatedI have a problem whereby I have a list of data that has a date, a transaction and a balance. I want to be able to rearrange this data into multiple tables, one for each month.
The pictures below explain the situation better, on the left is the list of data I need to sort, and on the right is how I'd like the data displayed. So for the May columns I would like to display all transactions that happened in May and, depending on whether it is an income or an expense, the amount in the corresponding column.
i.e. so the finish result looks something like this:
I have a table that looks like the following, only it's actually much larger:
_Red Blue Green Blue
A 2 4 2 3
B 5 2 1 1
C 3 1 2 5
D 2 3 4 2
As an example, I'm trying to sum all cells that match Blue and C. The answer should be 6, but I always end up with either zero or #VALUE.
I have a spreadsheet where I am tracking several entries in a table that will keep growing. Three fields are Data Validation Drop Down Lists. The macro below works well to clear the two lists to the right when the first one is changed by the user.
[Code] .....
I want this to affect the rows below it in the table as they are added.
I have a workbook that contains
Tab 1 --a datatable with mulitiple suppliers. Data changes once a week.
Tab 2-25 --Report Tab for each client--to be sent out to client weekly to update status of deliveries.
Current process:
1. copy and paste data table into excel model Tab 1
2. use advanced filtering to pull data from the datatable into each tab one tab at a time--25 times!
3. Sort the date in the report tab--25 times
4. Format the report tab--25 times
5. Copy and Paste into a separate workbook--25 times
6. Send each report--25 times
How do I reduce the number of steps in this reporting process?
I've got this code that pulls data from multiple files into one table. the file name is in the top row, and each file's title, and two different sums display below that. Then it is supposed to loop through and display each task and the start and end date for that task within each file. After that the first loop brings it to the next column and file. The primary loop works fine, but the inner loop only seems to run once, as I get only the first result for each file. All I can figure is that maybe my row numbers aren't resetting like they're supposed to, or I'm completely overlooking something, which is likely, as VBA is by no means my forte.
Sub Worksheet_Calculate()
Dim sBook As String
On Error Resume Next
Application.EnableEvents = False
Col = 8
' Sets start column
Do
sBook = Cells(2, Col)
' References file name
TaskRow = 6 ........................
I have a set of data (assume 2 columns, one with a long name and the second with a time). The names contain variables that must be used as criteria (a single entry may contain "Blue" and "On"/"Off") and times vary, based on when the Name turns "On" or "Off" [in minutes: 25 (On), 47 (Off), 89 (On), 100 (Off) and 137 (On)]. I need to create another table that automatically inserts values for all times, to include the missing times (0 min thru 24 and 26 thru 46, etc.). EX: If the first entry is "Blue-On" at "25 minutes", the cells from 0 minutes to 24 minutes are each "0" and become a "1" at 25 minutes - the following cells are "1" until 47 minutes (where it is turned off). (1 and 0 represent "On" and "Off", respectively)
How can I created a formula to insert the correct numbers into the correct places? I'm willing to have multiple cells with formulas and simply hide the columns that are doing the calculations.
I'm currently doing a survey using an excel workbook that contains multiple questions across multiple worksheets using radio buttons linked to certain cells.
I have around 400 workbooks coming back to me, so what i want to do is take specific values from across many worksheets within each workbook and combine them into a large master table in a seperate workbook.
I've tried using VBA, but not being very proficient at it i've hit a brick wall with that, so i'm hoping that there is an easier way to do it than what i'm currently pursuing.
Vlookup a table, to another table that has multiple values.
E.g.
Table
GPASP002
Look up table - need to return second coloum but not the first value, all the values?
GPASP002 KZASP100
GPASP002 KZASP500
GPASP002 KZASP600
GPASP002 KZASP501
GPASP002 KZASP502
GPASP002 KZASP601