Multiple Rows With Different Week Endings - Spread Data Horizontally

May 6, 2014

I have data in columns E through J under the headings of Monday-Saturday (E is Monday, F is Tuesday...J is Saturday). Then out to the left of the data, in column A, I have the week ending date (using Sunday as the last day of the week) which corresponds to the data under each day. There are multiple rows with different week endings but all the data is under columns E-J with the week endings in column A. So for instance, on row 13 the week ending in column A is 12/22/2013 (a Sunday) which means the corresponding data in the same row under columns E-J (the Mon-Sat columns) belong to 12/16/2013 (the Monday in the week ending on 12/22/2013), 12/17/2013 (the Tuesday in that week), 12/18/2013...and so on and so forth until 12/21/2013 which is Saturday in the week ending of 12/22/2013. Lastly, out to the right starting in column N and going horizontally out to column FD (will go out further as more days are added) I have the specific dates by day, it starts with 12/16/2013 and goes on incriminating by 1 day at a time until the end of all the dates included in the data (which at this point so happens to be 5/11/2014).

What I need done now is to take all the data which currently sits in columns E-J (the Monday-Saturday columns) and copy and paste it out under the correct specific date column that it belongs to starting in column N (going all the way out to FD) based on the week ending in column A. So with row 13 since the week ending in column A is 12/22/2013 the data in columns E-J needs to get pasted to row 13 columns N-S, then the same thing for row 14, except the week ending in column A row 14 is 1/5/2014 so the data in columns E-J row 14 needs to get pasted to columns AB-AG (AB is 12/30/2013...and AG is 1/4/2014). Below is the loop I came up with that accomplishes this, but for the reasons already stated.

Code:
Sub newtest()
Sheet2.Unprotect
Dim drd As Long
Dim rrd As Long
drd = 13
rrd = 14
Do Until IsEmpty(Cells(drd, 1).Value)

[Code] ..........

View 8 Replies


ADVERTISEMENT

Sorting Multiple Rows Horizontally?

Apr 11, 2013

I have over 300 rows with a username in the column A followed by 3 fields in the following columns B,C,D. I want to just sort all 300 rows alphabetically by column B,C,D.

Eg. Cormac, Santa, Atlas, Egg
After search Cormac, Altas, Egg, Santa.

how to do this easily in Excel without VB as I am not good as it.

View 6 Replies View Related

Excel 2010 :: Multiple Entries In One Cell That Need To Be Spread Across Multiple Rows

Jun 17, 2014

I need to convert data from column IDS into separate rows, all other columns need to stay in tact. There are several distinct patterns for the IDS column, main identifiers are always starting with FILER or TEAL and the trailing numbers behind it have no more than 6 digits.

BEFORE MACRO

ID
AREA
TYPE
CLASS
QTY
IDS

1
COAL
TYPE9917312
CLASS881345
2
FILER756911**/**FILER123188 ^** FILER877119*118

[Code] ........

AFTER MACRO

ID
AREA
TYPE
CLASS
QTY
IDS

1
COAL
TYPE9917312
CLASS881345
2
FILER756911

[Code] ......

What the MACRO would look like? This is for Excel 2010.

View 3 Replies View Related

Organize Raw Data Spread Over Many Four Columns In Rows?

Dec 5, 2013

I have raw data converted from pdf into excel Workbook spanning into many sheets.Each sheet having 5 sets of records. It is highly disoriented.Some times it is in 4 columns and some times in three columns having 30 unique Records which I Want to put in rows neatly in a single sheet .Raw Data is like in table below.In some Columns There are only 20 Records.I want them in rows organized under headers.

Name: Aa bb
Fax:*5555
Team:United
Actor:Arnold

[Code]....

Problem is that in some columns there are only 10 fields present,eg: name field is there Add,state,Country is not.Also in some columns many fields are in a single cell,eg: Tel,fax,Email in a single cell.

Each Record That Needed to extract starts after : Also I can Replace all required fields like name ,add, city, to 1,2,3 if that is going to work.

View 1 Replies View Related

Copy Formula Horizontally Across Rows

Nov 21, 2008

I have a percent value in cell E3, for example, then other numbers in cells H3through AA3. The formula is for all numbers in cells H3 through AA3 to be multiplied by the percent value in cell E3.

When I try to copy the formula entered in cell H3 to the rest of the cells across the same row, it does not copy as I would like. Instead of having all cells multiply cell E3, it will progress through each next cell. How can I format all cells I choose in row 3 to multiply the same cell, E3?

What I want is to be able to change the percent value in E3, for instance, and have all other cells automatically change values according to the percent they are multiplying in cell E3. I have about 160 rows I want to do this same thing in.

View 3 Replies View Related

Return Multiple Values Horizontally?

Jun 5, 2014

I managed to found an array formula that return multiple values with a condition. Below the formula:

[Code]....

I am attaching a simple file. In cell O4 I paste the formula, and I drag it as much as I need. But my issue comes when the formula begins the calculations. I need to calculate 50 columns with over 9,000 rows each column (over 45,000 arrays). Imagine the processing load to my computer and the consuming of time.

Attached File : Return-multiple-values-horizontally.xlsx‎

View 6 Replies View Related

Eliminate Endings From Part Numbers

Oct 31, 2008

I am trying to find a way to eliminate endings off of part numbers.

Below are the list of endings that I need to be eliminated from an extremely long list of variant part numbert. This list below may need to have additional endings added, but nothing will be removed. Please see attachment for example of part numbers that need below endings eliminated. Thanks!

R
T
G4
E4
RG4
RE4
TG4
TE4
G6
E6
RG6
RE6
TG6
TE6
/2K5
/3K
/250
/500

View 14 Replies View Related

Copy Rows Formula Horizontally With Pattern Using Fill Handle?

Mar 2, 2014

b2 = a2 * 150
c2 = a2 * 145
d2 = a2 * 140
e2 = a2 * 135

[Code].....

in the first row i want to use the fill handle at 135 and drag rightwards so as to copy/retain the pattern (decrementing by 5, from multiplier 150 until 100)

how would I achieve that?

p.s I also want the formula for columns to work with the fill handle drag downwards.

View 1 Replies View Related

Excel 2007 :: List Multiple Folder Horizontally

Aug 1, 2013

I am using excel 2007. I have multiple folder with multiple files inside like below

folder1
file1
file2
file2

folder2
file1
file2
file3

folder3
file1
file2
file3

i need output like

folder1 file1 file2 file3
folder2 file1 file2 file3
folder3 file1 file2 file3

I need to use a function like =filelist(a1) or =filelist("folder1") then it should list the files horizontally like above.

View 6 Replies View Related

Excel 2010 :: Looking Up One Value And Need To Return Multiple Values Horizontally

Mar 28, 2014

I have in column A duplicate values and in column B different responses (Sheet: Lookup). I need to look up the value in column A (Sheet: Results) and bring back all the responses in column B (horizontally).

Nittie Query.xlsx

View 5 Replies View Related

Excel 2007 :: Lookup One Value And Returning Multiple Values (Horizontally) With Two Sheets

Jul 31, 2014

I'm basically working from 3 sheets for this so I'll start with an example of the data I'm using:

1st sheet:

table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;

[Code]....

I have 180 rows of data like this one in the first sheet

Second sheet(named sheet 1) is not used for this

Third sheet(named sheet 2):

table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;

[Code]....

Basically I'm trying to find column #3 value in my third sheet and return the second column value. Problem is that the data is located more than once in the third sheet so I need the value of each one of them. So, with this example, Q5942X is located twice in the third sheet and each time, it has a quantity of 2. I would need either to return the value 2 twice horizontally or even better, add the two together. The first sheet, the example is row #45.

This formula should be in the column following "majoration".

I am using excel 2007 and windows 7.

View 3 Replies View Related

Multiple Operations And Formulas In Spread Sheet

Jul 18, 2007

I need to create a spread sheet that in Col A has 3 variables, each of which I need to triger 1)fill of that row, 2)different formula's in different columns within that row. Is this possible in excel?

View 9 Replies View Related

Mirroring Vertical Data Horizontally

Aug 20, 2008

I have one spreadsheet in which data was inserted vertically, and in order to move it to SQL, i would like to first mirror it horizontally in a 2nd spreadsheet (the reason i want to mirror it and not just copy the data is that the information in the first sheet changes dynamically).

So i figured out the basic function - (=sheet1!B0), but doing this on more than a 400 cells could be a problem. So my question is, how can i make, and lets take an example, cells A1 through I1 (in spreadsheet2) mirror A1 through A9 (in spreadsheet1), without editing functions cell by cell?

View 6 Replies View Related

Macro To Spread Amount Over Multiple Months Using Working Days As Weighting

May 5, 2014

Phasing Design.xlsx

We have a requirement to spread/phase amounts over multiple columns (representing months) using a weighting (represents working days per month).

We will calculate the weighting result in excel first (represented by a % per month), so the macro will be more of an allocation of row amount against the equivalent column %.

So far i have the following code:

VB:
Sub phasing()
Dim SourceA As Range
Dim Weight_ResA As Range
Dim TargA As Range
Dim i As Long

[Code] .....

View 2 Replies View Related

Transpose And Consolidate Data Horizontally To Vertically?

Jun 4, 2014

In the attached spreadsheet, I have the original data display horizontally (sheet2). Col A is Patient #. The header in row 1 are the test codes. Each patient took only 1 test and have result reported either neg, pos, pending or not eval. How do I transpose the header and have the test results consolidated in 1 column accordingly as display in sheet 3.

View 4 Replies View Related

VBA Or Formula To Summarize Data Horizontally Into A Table?

Feb 11, 2013

Macro 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.

View 1 Replies View Related

Multiple Ccolums/rows To Get Data From Multiple Columns/rows (vlookup)

Jan 15, 2010

I have created a spreadsheet to show some reports and I wanted to serch for some datas which overloops themeselves. If you can have a look at a test file I attached you will see the full picture. I have 2 tables, where the 2nd one is on the right side of the 1st one. 1st table:..............

View 3 Replies View Related

Move Data From Vertical Column To Line Up Horizontally

May 20, 2008

How do I move data that is in a vertical column to line up horizontally (in separate columns, but same row)? For example, take an address:

Name
Address
City/St
Zip

How do I get it to go from that format to this format?

Name Address City/St Zip

View 9 Replies View Related

Running Multiple In VBA Instances: Controlling Spread Sheet Always Waits For The Code To Return Before It Continues

Apr 28, 2009

i have a spread sheet which has a very complex simulaton in it. excel is not the ideal place for it, but it is not possible to port it wholesale to something more sensible. i have modified the code so that it is possible to run two copies of the spread sheet (in two seperate excel processes) at one time. Due to the dual core nature of the machine this has almost no overhead.

I have created a controlling spread sheet that is cabable of launching multiple copies of the simulation and starting the code executing. the problem is the controlling spread sheet always waits for the code to return before it continues, hence it would open two seperate versions of excel, start the first simulation executing and then wait until it completes before starting the next one. is there any way of starting a function in another work book without waiting for the execution to complete?

View 2 Replies View Related

How To Spread Existing Data Evenly Throughout The Same Row

Sep 28, 2009

I don't know how else to ask this so I will just tell you what I'm trying to do.

-I have a list of name on one column say "A"
and this list of names goes through "A1" through "A145"

-I am taking those names and pasting them via "right click" "paste special"
"Values" onto another spreadsheet.

-BUT I have to take each name one by one and paste them evenly
in every 8th row for example ( "A1" A8" "A16" "A24" ...)

-Is there a way to take the existing data on that row and just spread it evenly throughout the same row? So I don't have to take names one by one by one...

View 9 Replies View Related

Summarize Data Spread Across Workheets

Oct 23, 2006

Is it possible to consolidate data from various worksheets into a single worksheet using multi reference points or lookup_values?

For example, I have 2 worksheets named Section A and Section B in which I have detailed monthly expenditure details (e.g. overtime, allowance, uniform, etc) for each sections, respectively.
Then in the 3rd worksheet, called Combined, I have 2 columns - first is 'MONTH' and second, 'SECTION'.
Now, suppose I have a 3rd column in which I want to pull over the overtime expenses for both sections. That means, my VLOOKUP must first look for the SECTION worksheet, then find the MONTH before returning the value found in the overtime column.
How can I do this?

View 9 Replies View Related

Report Data Spread Over Many Worksheets

Dec 4, 2006

im creating a spreadsheet at work that gathers project and FTE figures.
I have attached a copy, but ive had to totally simplyfy it to get it to the required uploadable size.

Basically each team member goes into the sheet tab with their name on it (the full version has about 40 sheets), enters the total hours per week they will be spending on each project.

Then the manager opens the spreadsheet, clicks on get data then enters the date. Data for the selected date is then shown. However I need excel to go into each persons sheet, take the names of the projects that will be worked on (obviously the ones that are not blank) then report the names onto the front sheet under "name of projects".

can anyone help me with this? as you will see, im self taught with vba etc so its probably not the best but im trying.

View 9 Replies View Related

Sort Data Spread Over Two Sheets

Jun 25, 2007

I have two sheets within one workbook, mean I have 300 columns, as u know excel sheet is limited to 256 columns, so I divided the columns in 2nd sheet, now I want to sort the data, but when I sort the first sheet data, the second sheet data not sorted,

View 6 Replies View Related

Importing Data From A Website To Spread Sheet

Jan 20, 2008

Im trying to import data from a plumbing supply website to an excel spread sheet.

On the supply house web site, for each part catgory there are corresponding pages containing a price list data table.

there are from 1 to 3 pages (price list data tables) for each part category.

i can import data from page 1 and page 3 fine. When i try to import data form page 2, it returns no data, or imports page 3 (instead of 2)

I spoke to the person who designed the website and he said that it was a problem within excel and not a problem with his program. (said it was a problem with excel not being able to recognize the page)

View 9 Replies View Related

Pivot Table: Data Spread Across Different Worksheets

Nov 11, 2006

I have a spreadsheet with data across multiple worksheets and I am trying to figure out how to use the data from the multiple spreadsheets to create a single pivot table on a separate worksheet. I cannot reasonably bring all the data into one workshet because each one has thousands of rows. I want to pull data from select columns in each worksheet. How can I isolate only the data I need from different worksheets into one pivot table?

View 3 Replies View Related

Unmerge And Spread Merge Data To All Cells

May 16, 2007

In the attached document, there are some cells which have been merged. For instance the cells A4 thru A7 were merged as one field. if you examine B4 which is actually made up of B4 thru B7 the data contained therein is 123. Is there a way to unmerge these merged cells and have the value (in this case 123) appear in each line of the unmerged cells? If you right click and click unmerge, it unmerges okay but does not populate each line with the data which was on the original field.

View 3 Replies View Related

Lookup Row And Column As Criteria But Data Is Spread Across Columns

May 30, 2007

I need to bring in values into one worksheet from another worksheet using row 1 and column A as criteria. I have previously done this using Sumproduct. However the complicated issue with this new worksheet is the setout. The source worksheet (see attached) has the actual data spread across columns. How can I bring in the values to the 'Summary' sheet from the '0607' sheet using the Employee number and the seg code as 2 criterias to lookup and bring in the resulting data (which is spread out in the purple area in the '0607' worksheet).

View 8 Replies View Related

Macro- Large Amount Of Data In A Single Spread Sheet

Jul 17, 2008

I have a large amount of data in a single spread sheet. Each row has a branch number on it, there are multiple branch numbers. Each branch number is located in Column A. I want to separate this sheet and put all of the branch numbers into their own sheet. how to do this without manually copying and pasting?

View 9 Replies View Related

Count All Rows (dates) That Will Come Due In A Week

Mar 27, 2009

Worksheet is an action log. It has a column containing "due dates". I want to count all rows (dates) that are "less than" today (to see what work is overdue). I also want to count all rows (dates) that will come due in a week. Conditional formatting highlights them OK but I also want to maintain a set of counts. I can do all this in a macro but I don't want to use macros, just formula.

View 2 Replies View Related

Match Week And Count Rows With Yes Or No.

Jan 12, 2010

I am trying to produce a excel spreadsheet on Excel 2003 that has sales data. At the end I am to produce where my supervisor can produce a summary of the data showing the last weeks work (and only the last weeks) and how many yes rows there were and how many no rows there were for that week. There is a lot more data involved but the only relevant data for the problem I have is the date and Yes/No fields.
I have looked on other sites and the only thing that I can find that is close is the following formulas.

=COUNTIF(Sheet1!A:A,">"&E1-7)-COUNTIF(Sheet1!A:A,">"&E1)
=SUMIF(Sheet1!A:A,">"&E1-7,Sheet1!C:C)-SUMIF(Sheet1!A:A,">"&E1,Sheet1!C:C)

Apparently these will count the rows and sum them. However they don't differentuate between yes and no. I have attached a very basic spreadsheet with the problem.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved