This particular formula is used several times to gather various data points across 3 different sheets named Month-1, Month-2 and Month-3.I gather the data the following 3 formulas then sum it as needed with a helper cell.
=IFERROR(INDEX('Month-1'!$A$1:$G$6000,MATCH(TRUE,INDEX(1/('Month-1'!$A$1:$A$6000=$G$24)*ROW('Month-1'!$A$1:$A$6000)-MATCH($A96,'Month-1'!$B$1:$B$6000,0)>0,),0),MATCH($M$2,'Month-1'!$A$14:$G$14,0)),"")
[code]....
I have an excel with several sheets with task for different departments. All of them are assigned to a person responsible for that task. I applied auto filter which gives me the overview for a person Per department, now i would like to gather all departments into one so we have an overview for each person what his tasks are.
View 1 Replies View RelatedI've started building a macro that loops the sheets and collects the information onto the first worksheet. I've been using Activesheet and activecell references but i'm afraid looping will change these references.
[Code] .....
They sent me a workbook with a lot of information including Employee Name, Position, Supervisor name and Organizational Unit (in columns A, B, C and D respectively). With this data, I need to create a "Tombstone" for each supervisor with his/her name, organizational unit, and the name and count of each position he/she manages. For example:
John Smith
IT
Analyst - 10
Programmers - 15
Now, I was able to easily get the names of all the supervisors and their respective organizational units in the tombstones. However, I'm stuck trying to find a quick way to enter all the positions each supervisor manages and their count. And with more than 300 supervisors in my company, doing this manually would take too long.
I have an excel file which contains two sheets. In one sheet there are some numbers in a single column. In the other sheet again there are certain numbers span across rows and columns (a number may be duplicated). I need to check whether there are numbers which exist in both the sheets. If such a number is found it may be marked (say with a color) in both the sheets.
View 2 Replies View RelatedI'm trying to create a pivot in 2010, which gathers a cumulative amount from data sources within two different worksheets.
View 2 Replies View RelatedSo I have this problem in excel with comparing 2 columns.
Basically, I have 2 columns(a &B) that I need to compare with one another and find out the matching data. I am trying to use that matching data to enter in our reports.
Both columns might have duplicate items because I am dealing with premium numbers.
Formula to compare these 2 columns and find the matching data and extract it to a separate column. Keep in mind, i am dealing with almost 20,000 lines of data.
I have a lot of rows of data all divided with an empty row and if they are 6 or less all rows need to be entirely deleted any 7 or above need to be kept.
View 2 Replies View RelatedI have a large spreadsheet where some data are similar. for Example
Robert $1000
Louis $600
Glen $350
Doreen $2300
Robert $500
Louis $600
Glen $750
Doreen $300
Robert $3000
I want to merge all Robert together, all Louis together and show as one Robert $4500. How can I do that ? Should I use a Look up.
I have a spreadsheet that has descrptions in column D. Where the descriptions are the same ,
I need the VBA code that will do the following:
1) Sort the data by columns C (descending), G (ascending) & E (ascending)
2) Putting a border around all the data where the decriptions are the same
i.e from column A to Column I beginning from row 2
I have attached a sample file for ease of reference....
Column A: List of document numbers
Column B: List of amounts
A B
111 $5.00
222 $10.00
111 $5.00
222 $15.00
I need to merge all equal document numbers so that it shows the full amount on that document number. It needs look like this in the end:
A B
111 $10.00
222 $25.00
I realize a pivot table will do this but the formatting of a pivot table will not work with what I am ultimately trying to do.
I have ~1,000 rows of data utilizing columns A:C. The data is "grouped" by data that is in column C. Layout is as follows for example
Row01.....Column A......Column B.......Column C
Row02.....some data.....some data.....apples
Row03.....some data.....some data.....apples
Row04.....some data.....some data.....apples
Row05.....some data.....some data.....apples
Row06.....some data.....some data.....oranges
Row07.....some data.....some data.....oranges
Row08.....some data.....some data.....oranges
Row09.....some data.....some data.....oranges
Row10.....some data.....some data.....pears
Row11.....some data.....some data.....pears
Row12.....some data.....some data.....pears
I have been trying to come up with some code that will "select" groups of data and then perform an action on those cells. As an example:
I am trying to merge two cells that have the exact same content in each cell. Everytime I do this, I get a pop-up warning that Im trying to merge cells with content in them and that only the upper-left content will remain. This is exactly what I want, without that pop-up, as I have to do this for multiple columns at a time.
View 2 Replies View RelatedI've got a pretty tough problem I need to solve with excel. So im comparing prices of products from 2 Different Sources, I need the best method of comparing these sets of data. Realistically I need both sets of data to compare and move automatically (or create duplicates elsewhere) to show the product ID, Price A and Price B.
Here's an example
Supplier A
Cheese Grater: 39.99
Golden Spoon: 129.99
Supplier B
Cheese Grater: 59.99
Golden Spoon: 89.99
These values will then combine to read
Supplier A Supplier B
Cheese Grater: 39.99 59.99
Golden Spoon: 59.99 89.99
Not sure if it will matter but both lists are not identical, all I want are the matches not the ones unique to different suppliers.
This comparison list is like 12,000 products long, I'll never be able to do it manually. This has stumped me for a while now I only seem to be able to find formulas which give TRUE / FALSE or Colour changes.
I need a formula that looks at 2 columns and if the data is not similar to return the value.
For example
Column A
joe Bloggs
Column B
Bloggs, joe
Column C
if value in column B is not similar to column A to return value of column B
I have a set of data that looks like this:
A | B | C | D
---------------
100 | 2 | 0 | 0
100 | 0 | 0 | 7
100 | 6 | 0 | 0
101 | 8 | 0 | 0
101 | 0 | 5 | 0
101 | 0 | 0 | 1
Column A is a 'User Number' and columns B-D contain values.
I was hoping to use this data to create a new range where each 'User Number' only has one row, and the values in the other columns are summed for each user.
This would ideally look like this:
A | B | C | D
---------------
100 | 8 | 0 | 7
101 | 8 | 5 | 1
I have to compare and combine data from multiple files and combine it into 1 master spreadsheet. I have attached a sample file. It is in bulgarian, but the language is of no importance. An example of the file: it contains data about repair maid on a truck. First column is just the number of the repair type and shall be filled upon completion of the table. the second column is the name of the repair itself. the 3rd column is the date the repair was made. the 4th column - the total work hours the repair was made. Some repairs were made more than once and thus the merged cells 3 to 9 in column B. I have 30 trucks which have more or less the same repairs, just a different number of each repair. A sample file of 1 truck is attached, called 1truck.
The master spreadsheet should be of the same type as the example, however showing data for all trucks (from different files) next to each other. (the attached "alltrucks") As you can see there are repairs done only on some trucks, and others are done multiple times. The master sheet should have all possible repairs from all trucks. So far I've been doing it manually, but takes way too much time...
I have a plot of data in a chart. I've added a worksheet to the same workbook which has data that I want added to this plot. The data is in the SAME columns/rows, the only difference is the worksheet name. I'm looking for the quickest way to add the data to this chart as I have to do this many number of times. Perhaps if possible to somehow copy the
=SERIES("Title",sheet_name!$A$11:$A$18882,sheet_name!$R$11:$R$18882,5)
And paste it back in with just a sheet name change?
I need to sort data similar to below using the following conditions. If the total of A's is greater than 4 they get an A. If the total of E's is greater than 4 they get an E. If they have more A's than E's, assuming they have at least 4 of each then they get an A. If the total is 5, but they don't have 4 A's then they get an E
NAME E'sA'sTOTAL
John 235 ( so this one should get E)
Mary 459 ( This one should get A)
Tim 112 (This one shouldn't get anything)
Jane 145 ( This one should have an A)
Sam 415 (This one should have an E)
I would like this to sort in one cell - I can do this over 5 cells - but not in one.
I have a worksheet (titled "Data") that has data in columns A thru
Q. I'd like to create a command button that when activated would search column data from cell Q2 down and then identify any matching data that has 30 or more similiar matching entries. I would then need this macro to copy all of the row data across for all of the matching/applicable data and then copy it beginning in cell A2 onto a worksheet titled "Search Details". The difficulty is that filtering won't work as I need the macro to loop until it can identify any/all data that has 30 or more matching data. The type of data that would be in column
Q2 and below would be something like "Doe01/01/01", "Smith02/03/27", etc. Both the Data and Search Details sheets have headings in row A1 thru Q1.
I need help creating a formula to find data in one column that starts with different letters and counts them up.
For example one column would contain the words amoung others:
DPDE
DNPD
EPRO
EOTH
YBRC
YUND
In the other column I want to count up the amount of times a word starts with the letter D or E or Y. The count comes from another spreadsheet, which the CMRF Tool (See attachment) is pasted at the bottom of.
The Monthly case report file is the file I want to get the count from. These would come from Column O once the data is there. The count would end up in column D of the CMRF tool which is pasted at the bottom of the monthly case report file.
I have problems with my project. I want to filter data between two dates with customer ID and when click Export Report button , result will copy on next sheet ( sheet's name is report in my file attachment ) and auto sum similar products.
eExample_report.xlsm
I, daily get a list of individuals with some data against each one of them. E.g Amit would appear 7 times in the list, John would appear 10 times in the list and so on and so forth. I am required to sort the data as per names and then fill one background color. One color for one person so that it becomes easy to read data specific to an individual.Ia there a way that the color fills up automatically. note the names and number of entries are not same everyday.
View 8 Replies View Relatedi am trying to do collect data from 4 different spread sheets (they all consists of the same columns but they do not have the same amount of rows) and rank them based upon one of the columns in a fifth spreadsheet. in the fifth sheet i also want to display all of the information found in the four sheets, see simplified example below. Is this possible? and how do i do it? when i googles it i only found ways to do it using macro but i know nothing about macros..
Simplified example:
Sheet 1
A 3 W
B 5 X
Sheet 2
C 2 Y
D 7 Z
What i want excel to do
C 7 Z 2
B 5 X 1
A 3 W 1
C 2 Y 2
I have two work sheets as-
Sheet1
idnameclassscores
1abc280
2efg276
3hij555
8klm478
9mno490
Sheet 2
nameschooldate of admission
abcpublic school2/9/2011
efgpublic school3/4/2010
hijprivate school5/9/2011
klmprivate school8/9/2011
mnoprivate school9/10/2011
now what i want is - on sheet 3 compiled data as-
idnameclassscoresschooldate of formation
We have a software program that we use to takeoff HVAC equipment on drawings. It will export all of the items to an Excel worksheet. Once it is into Excel I need to sort the data by two or more different columns, then sum three to four columns for each unique item for transfer to the equipment sheet for pricing. I've searched this site and others, but have not found a way to do this with VBA in Excel.
Floor
Rank
Name
Description
Qty
Cost
Labor
Seismic
[Code]....
The sheet is a simplistic view of what I'm trying to explain, obviously in high rise there will be many floors and hundreds of items. The actual spreadsheet has approx 12 columns, but only 3 or 4 need to be summed per similar item. Most times I sort by Range & Name. On occassionwe need to sort by Floor, Range & Name. Used to do this with a Database & RR Report Writer, but the new software does it onscreen with digitized drawings.
I can use help creating the following forumla.
In column "A" I have Dates of Variances. In column "E" I have the variances for those dates.
On another sheet I need variances between certain dates. For example, get all variances between the dates 03/28/09 - 04/28/09.
I have a 11 worksheets in my workbook and 1 of them I'm using as a Index. All the others are for a individual person. Basically, I want to gather all the information from the 10 worksheets (but I want to keep the info on those worksheets) and transfer it all to my Index worksheet.
The thing is, I want to transfer a certain range to my index worksheet, and when I do so, it could automatically number the rows.
Let's say I add information on 3 rows to all of the worksheets and then gather them and insert them in my index worksheet. The next time I add information, I want to select all the information again and write it over the one I inserted before.
Example, I want to transfer range B5:K? (? being the last cell with info in it.) from all my worksheets to the range B5:K? on my index worksheet; writing over the old info on that sheet.
I'm trying to be as clear as I can.
Sheet 1
Sheet 2
UPC
Sku
[Code].....
I would like to find the value from Sheet2 Column1 in sheet1 Column1 and return value from Sheet1 Column2 and Column3 into Sheet2 Column2 and Column3
And if it doesn't find anything just return Not Found
The problem that a Vlookup is not working for me is because I want it to be the exact text from sheet2 column1 but in sheet1 column 1 it should not be exact as it might have some extra text as seen in the illustration above
I am using the following forumla, it gathers the net sum of a column of $ amounts.
I need to have it changed to gather the Absolute ABS total instead.
=SUMIFS($B$23:$B$500,$A$23:$A$500,">="&$V23,$A$23:$A$500,"<"&EDATE($V23,1))
V23 has a date "Month" value.