See attached template I am working on. I want to calculate the number of hours worked over specific dates e.g. between 19-25 May 2014 and 26-31 May 2014 for employee "Bird, Ken" - the result is to be placed in cell D19. Tried sumproduct but without success.
celeste template.xlsx
Sumproduct help with criteria in Columns and Rows
View 9 Replies View RelatedIs there a way to replicate this code, so I don't have to Do the With [CODE] .......
View 9 Replies View RelatedI've not used SUMPRODUCT previously and can't understand how to get results for the attached.
I've tried SUMIFS but it doesn't work because I'm looking down columns and across rows, I'm assuming.
I've attached a summary of what I'm trying to achieve. I want to sum all costs with an R,P,I,G, etc. in column C for December '13 (E3) in the top table.
The second table is actually in a different sheet but is the source of the data I need added.
Sumproduct P&L.xlsx
I cant seem to figure out why this sumproduct formula wwill not pick up the datatable on a separate tab. Data table is Column E to AA but it only goes to Column Y. If I try putting AA in - the formula returns the VALUE#. I thought it maybe the cell formatting but it does not seem to the problem.
=SUMPRODUCT((Worksheet!$C$3:$C$57>=$H$12)*(Worksheet!$C$3:$C$57
removing duplicate rows and move other data frm rows to columns.xlsx.
I am attaching a sample excel sheet showing what I need to do.In the first tab, I have a list that includes duplicate rows (first column only). I want to remove those duplicate rows but I don't want to lose the data in the following columns which can be unique or duplicates as well.
see the desired result tab in the sheet to get an idea of what I am looking for as the end result.
Keep in mind that the actual source file I am working with could have up to 50000 row, and the expected results could be around 2000 rows. So nothing can be done manually.
Column A is numbered 1 -100 successively Column B thru D contains data that goes with the assigned number in column A. I need to be able to move rows of data in column B through D to a different set of rows all at the same time (not one cell at a time) without disturbing the set numbers in column A. And with that, have all the other rows of data automatically adjust accordingly(not to be deleted or replaced).
View 1 Replies View RelatedUsing the following code to remove empty rows based on whether a specific range of columns is empty. The code works if the cell has a zero, but not when the cell is blank. An example of the data is attached.
VB:
Public Sub DelRows2()
Dim Cel As Range, searchStr, FirstCell As String
Dim searchRange As Range, DeleteRange As Range
[Code].....
When I was using Excel 2000, there was an Excel add-in where I could highlight rows of information and then transpose these into columns of information. Since we have upgraded to Excel 2003, the same Excel add-in does not work and I have not been able to find a simple solution to transpose my information from a horizontal view to a vertical view or the reverse.
Or do I just have to move 53 columns (weeks) of 4 rows one cell at a time to 4 columns of 53 rows (weeks)?
I am trying to sort out some item numbers with their page numbers.
My file is in the following format:
The title makes it sound simpler than it is. I have 2 columns of data which I need to transpose.
The difficult bit is that Reference Number 1 may have 3 reasons (so therefore 3 rows) that need transposing into 4 columns (reference number, reason1, reason2, reason3)
Is there a way to do this?
I have attached a spreadsheet with the original table and how I need the end result to look.
i have a problem in copying many columns to to rows that originally contains data, this is gonna be really complicated so here is wt i want, let's assume i have this table .....
View 6 Replies View RelatedI am trying to clean up some data, which is organized horizantally AND in rows, as per the attached screenshot.
The fact that there is data horizontally across seven week days and in rows for the weeks of the year makes it impossible for me to use the simple TRANSPOSE feature, of which I am aware...
I have quite a couple of worksheets I need to apply this operation on, so manual work is the worst solution...
I have an excel spreadsheet which contains data for customers and the last date they were seen at an appointment (along with various other bits of info).
The way the data is exported from my database package means that each customer has one row per appointment, i.e. row 1 contains john smith, 01/01/2009, row 2 contains john smith, 03/03/2009, row 3 contains john smith 01/04/2009, row 4 contains joe bloggs 12/02/2009, row 5 contains joe bloggs 27/03/2009. Some customers may have 4 or 5 appointments listed whereas another customer may only have one. I need to get the appointments all onto one row per customer so that I can calculate the number of days between appointments. I have tried to use transpose, but with 8000 rows it takes forever.
I am trying to convert some data...
I have a spreadsheet of 1000 rows on my spreadsheet and I am trying to convert them to 2 columns. Below is an example
Antidiarrheal AgentsBrand or SeriesApplicationGeneric Drug NameStrengthTypeDosage FormContainer TypeVolume
Antidyskinetics Brand or SeriesApplicationGeneric Drug NameStrengthTypeDosage FormContainer TypeVolume
I want it to look like this:
Antidiarrheal Agents Brand or Series
Antidiarrheal Agents Application
Antidiarrheal Agents Generic Drug Name
Antidiarrheal Agents Strength
Antidiarrheal Agents Type
Antidiarrheal Agents Dosage Form
Antidiarrheal Agents Container Type
Antidiarrheal Agents Volume
Antidyskinetics Brand or Series
Antidyskinetics Application
Antidyskinetics Generic Drug Name
Antidyskinetics Strength
Antidyskinetics Type
Antidyskinetics Dosage Form
Antidyskinetics Container Type
Antidyskinetics Volume
Is there a formula or a suggested way of doing a macro to acheive this?
I have a piece of equipment that I use for temperature profiling temperature controlled chambers. The equipment has up to 16 channels for logging data, although we typically only use 9. The data output from the equipment is by Text file and we convert this to Excel for manipulation into a format that can be issued as a certificate.
The test file outputs the data mainly in a single column and similarly when it is converted to excel the data remains in a single column. To enable us to present the data in a format that is presentable as a certificate we most transpose the data from each channel from a single column into an individual columns for each channel. Because I have limited knowledge of Excel, this operation is very time consuming and I'm sure there must be a quicker way to perform this operation in Excel.
I have attached a copy of the text file and the resulting Excel file with the data in it's original format and also in the final format. This is only a snapshot of the data over 30 minutes and we normally carry out measurements over 24 hours.
I have the following spreadsheet with 4 columns of data:
1000001245aMr Fred Blogs
1000001270a1 Blog Street
1000001270bNorwich
1000001270cNorfolk
1000001270eNRx x99
1000001270gThe Manager
1000001270k01603 555 555
1000001270l01603 555 555
1000001270qManager
1000001270xN
1000001270yN
1000002245aMrs Fredilina blogs
1000002270a10 Blog Street
1000002270bNorwich
etc etc for about 36,000 rows.
What I need is for each unigue reference ID (column A),
I need all corresponding data moved up into a single row in serperate columns, something like this:
1000001 |245|a|Mr Fred Blogs| 270|a| 1 Blog Street|270|b| Norwich | 270|b|Norfolk |etc
1000002 |245 |a |Mrs Fredina Blogs |270|a|10 Blog Street |270|b|Norwich| etc
Each entry doesn't nessesarily have the same number of rows.
I would class myself as a beginner VBA bod, but the solution to this one is illuding me a present.
I'm using Excel 2007 and I need to extract values from rows from a data dump into columns. Here is sample data (this is dummy data, but the format is the same): ...
View 13 Replies View RelatedI have a spreadsheet that consists of columns of data and I need to covert it into rows depending on how the data is set up. In column A, there is a list of invoice numbers. Columns B, C, & D are as follows:
Column B: Sales Rep Name
Column C: Sales Rep Number
Column D: Sales Rep % Split
I want to have a single row for each unique invoice number. As an example, I have the following:
Row 1: invoice ABCD-1234 Sales Rep 1: 55%
Row 2: invoice ABCD-1234 Sales Rep 2: 25%
Row 3: invoice ABCD-1234 Sales Rep 3: 20%
I want the output as follows:
Row1: invoice ABCD-1234 Sales Rep 1: 55% - Sales Rep 2: 25% - Sales Rep 3: 20%
I have attached a sample workbook with two sheets; one containing the raw data and the second, which shows the desired output.
I really could use some help here. I have ~25,000 rows of data, contact information, and I am trying to convert it to columns with the corresponding 9 column headers (Full Name, Job Title, Company, Business Street, Busines City, State, Business Phone, Business Fax, Web Page, E-Mail Address). The challenge is that each record does NOT have the same number of rows...?? I am new to this forum, so please forgive me if this a stupid question. I've attached the data for three records below and also in the attached spreadsheet ...
View 14 Replies View RelatedI have a single sheet that has 6 fixed columns of data in but the number of rows can vary per user per import. Users can actually insert rows as required. The seventh column has my macro buttons present. It looks and works great for about 20 rows (one screen) full. If the number of rows gets to be more than that and the user needs to scroll to modify those lower rows the macro buttons roll off the top and are not visible to access. Is there a way to keep the macro buttons in the same location (column G) and only have the data (columns A-F) scroll when required by the user? This way the macro buttons are available on the rows beyond the first 20 or so for the user to use, instead of having to scroll back up to use them. I've tried using the split and freeze functions but neither worked.
View 5 Replies View RelatedI have an Excel sheet that is in rows. I want to separate the data into columns. Each sample is separated by a space in the rows. So sample 1 is A1 is the name and B1 is the actual name. A2 is the description and B2 is the description. Each sample is +or- rows. Sample 1 may be 8 rows but sample 2 may be 6 rows. What type of formula or macro do I use to arrange the samples into columns instead of rows so the data will be like A1 name, A2 description, etc... and A2 will be sample 2 name, and B2 will be sample 2 description, etc...
View 7 Replies View RelatedI am trying to figure out an efficient way to convert data into Excel. The data is not in a row/column format. The format that I received the data in is as follows (2 columns; column labels repeating for each name in column A, and the corresponding data in column B. See below. Any ideas on how to best to convert this data into column labels across the top and data in rows under each column heading?
Column A Column B
Name John Doe
Address 123 Maple Street
City Any City
State Any State
Zip 12345
First Name John
Last Name Doe
BLANK ROW
Name Jane Doe
Address 123 Maple Street
City Any City
State Any State
Zip 12345
First Name Jane
Last Name Doe
I have searched and have not been able to find exactly what I need. I currently do this manually and it is taking too much time.
I start with a file (sheet1) that has data in column A for a location.
Column B has a unique 10 number (each location can have between 1 and 10000). Example:
Location,secnumb
1000001,10052950
1000001,10052960
1000002,10052980
1000002,10052990
1000002,10053000
1000002,10053010
1000002,10053020
1000004,10053530
1000004,10053540
1000004,10053550
I need to get this data into the following format for a mail merge:
Location,secnumb1,secnumb2,secnumb3,secnumb4,secnumb5
1000001,10052950,10052960
1000002,10052980,10052990,10053000,10053010,10053020
1000004,10053530,10053540,10053550
I was thinking I would set up a sheet2 with all the unique location codes, and then do a match between the location code on sheet 2 and sheet1 and use an offset function?
But I am way over my head when I work with vba.
way to take a long list of the following data and change it from rows to columns?
First name Last name
Address
City, State Zip code
First name Last name
Address
City, State Zip code
That is how the data is currently displayed, spaces inbetween as shown but, long list of names and addresses. There is also an occasional 4th line usually for an address. My instructions are to put data in the following format:
First name Last name,address,city,state,zip code,
I have train departure times stored in rows for each date as follows:
Mon02-Oct8.399.069.4210.0910.3611.12
Tue03-Oct8.399.069.42
Each time/date is in its own cell.
I have hundreds of entries like this and what I want to do is restructure them as follows (on a new sheet). For each date and time couple I want the date in one cell and the time in the the cell to the right. Also please note that not all days have the same amount of dept. times and this also needs to be considered.
e.g...........................
I have an excel file with data that was entered in rows instead of columns and I need to rearrange the data into columns. Here's what my data looks like now, with what should be field names in column one and values in column two:
Company Name : Excel Company
First Name : Jack
Last Name : Smith
Phone : 555-661-6674
Email : ozgrid_rocks@excelgods.com
(this continues in a repeating pattern for thousands of rows)
I need the data like this:
Company Name : First Name : Last Name : Phone : Email
Excel Company : Jack : Smith : 555-# : ozgrid_rocks@url
I have data which is in rows. I want them to be in 5 columns.
I am attaching the sample.
for some of the entries, there will be 6 rows:
Company Name - 2nd row
Address - 3 row.
In this case, I want to combile them as one entry Separated by a comma
& placed in one column
Eg: For 3rd one in sample,
Haifa Marble & Tile
69 Garfield St
Wanted Result: Haifa Marble & Tile, 69 Garfield St
If this is not possible, then suggest how to combine two columns to make it
into one column separated by comma.
i have information across three columns the first has user-names in each row the whole way down, the second has between 1-7 activity codes (when not eacher user will use), the third has the times they have been on these codes.
what im trying to do is match the name, code and get the time to be displayed in a fix table, as the reported information is not always in the same structer
eg
user1 code 1 0:02:00
user1
user1
user2 code 3 0:05:00
user2 code 6 0:20:00
user2
now i've got it in my head that sumproduct iwll be the best way to get it, but i cant seam to get the third array to work properly, and always comes up with either value or NA
I have a huge ss and my sumproduct function works only up tp certain columns and starts returning #value! error. here is the formula:
=SUMPRODUCT(--(Detail!$A$2:$A$2971>=$R$4),--(Detail!$A$2:$A$2971