Distribute Across Columns Based On Number

Dec 20, 2006

I have a list of parts with quantities that i would like to distribute among several columns that represent cities, based on the number of parts in stock. For Example, if i have 5 parts and ten cities, i want it to put one part in the first five. In the case where theres more parts than cities i want it to loop back to the first city and add one and so on. I attached a sample spreadsheet.

View 5 Replies


ADVERTISEMENT

Distribute Number Based On Score?

May 14, 2014

I am trying to distribute the total number of sessions (cell H1) across the groups in column A based on the score of each group (column D). The number of sessions a group receives cannot exceed the max (cell H2) or be less than the min (cell h3).

View 7 Replies View Related

Distribute Data Columns To Rows

Feb 15, 2010

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.

View 9 Replies View Related

Distribute ID Based On Criteria?

Mar 10, 2014

I have a workbook that needs to fill the column B (if the column A is populated) with Column C if the column D is "YEs" once i clicked the command button. the distribution should be even. i tried rand() but its not distributively well, so i'm thinking to work it with macro.

View 10 Replies View Related

Vary Columns Based On A Number

Feb 5, 2009

You can always add numbers together. however, how can you vary columns based on a number?

for example, i can always do things like the following to change the number of rows selected in a column based on the variable numRows.

View 2 Replies View Related

Loop Based On Number Of Columns

Jan 10, 2014

I have a table of data (lets say A2:H30) i want to populate so i want the macro to loop based on how many columns i have? I am using this to post the data based on # of rows..

Sheets("Main Cal").Select Dim bottomA As Integer
bottomA = Range("D" & Rows.Count).End(xlUp).Row
Dim c As Range
Dim ws As Worksheet
For Each c In Range("D3:D" & bottomA)
Set ws = Nothing
On Error Resume Next
Set ws = Worksheets(c.Value)
On Error GoTo 0
If ws Is Nothing Then

View 1 Replies View Related

Combine Two Columns Based On Name / Number And Score

Feb 19, 2014

I have three columns in the spreadsheet.

sheet1

A BC
Santhosh314
Praveen224
Praveen012
Praveen37
Santhosh38
Praveen120
Praveen036
Santhosh19

In another sheet (sheet2) output come like this.

A B
Praveen 3/7
Santhosh 3/8

View 2 Replies View Related

Capture Values Based On Number Of Columns?

Jun 7, 2012

My data comes from 3 different sources

Source A = 4 columns (3rd one is important)
Source B = 8 columns (3rd one is important)
Source C = 5 columns (4th one is important)

Is there a slick way to capture values based on the number of columns in that row?

View 1 Replies View Related

Insert Rows Based On Number Of Columns Used

Jan 31, 2008

I have a spreadsheet which contains data needing to be re-formatted into a suitable format for my database. So far I have been doing this by hand, but want to make it quicker as I have over 2000 lines to sort out. I am trying to write a macro that determines whether there is data in the column next to the active one (D2), and if there is counts how many columns there is data in. Once it has done this it needs to insert the same number of rows as there are columns, then copy the column data and transpose it into the empty rows. My macro seems to work at first, but it exits after around the 4th row, but I can't work out why.

Sub split_For_Database()
Dim No_Of_Cells As Integer
Dim Start_Cell As Range
Dim Cell As Range
Dim LastRow As Range
Dim i As Integer
Dim lRows As Long
Set LastRow = Range("C65536").End(xlUp)
i = 0
Set Start_Cell = Range("C2")
Set Cell = Start_Cell
Do While Start_Cell.Address <= LastRow.Address
Do While Cell.Offset(0, 1) > 0
i = i + 1
Set Cell = Cell.Offset(0, 1)
Loop...................................

View 8 Replies View Related

Trying To Count Number Of Occurrences Based On Content Of Two Other Columns

Jan 30, 2014

I am working on a spreadsheet that will provide count of types of complaints for particular areas over a running time span. I have tried a multitude of formulas but not sure how to write any of them correctly. What I am trying to do is generate a count of area type by whether it is formal or informal. (i.e. I want to know if there are x formal finish issues vs. y informal finish issues and so on.) This information will get charted and be kept "real-time" user input.

Type
Description

Concern

Formal
Informal

[Code] ....

View 6 Replies View Related

Hide/unhide Number Of Columns Based On Cell Value

Jul 17, 2007

If I have a value in A1 and run a macro [button], I want to have it hide a number of rows.

Each number is one column.

eg if A1 = 2 then B:C are visible, D:IV Hidden
if A1 = 3 then B:D are visible, E:IV Hidden
if A1 = 4 then B:E are visible, F:IV Hidden

up to 200 columns.

I tried it as a select case, but it is limited to just 22 cases which obviously not enough.

View 9 Replies View Related

Rearrange (sort) Columns Based On Number In Column Header String

Apr 3, 2014

I want to rearrange(sort asscending) columns based on numerical value in column header string through VBA macro. Please check attachment.

i.e. (Present Data)
# A B C D
1 col.1 col.4 col.3 col.2

(Output Data )
# A B C D
1 col.1 col.2 col.3 col.4

test.bmp‎

View 2 Replies View Related

Reference Cells Keeping Columns The Same But Change Row Number Based On User Input

Dec 10, 2012

Date
#
Lname
Fname

[Code].....

With that said, I want to put the row number of a user in (I have a few thousand on this spreadsheet, and all the = data be automatically pulled based on the row number I put in cell B1. So I'm hoping to write in cell B1 the following - "143" (without the quotes), and the remaining cells in column B automatically pull that data based on that, so it would look like the following

Row #
143
Name:
=C143&" , "&D143&" "&E143

[Code]....

How do I write the functions to keep the columns the same, but change the row number based on the number I input?

View 3 Replies View Related

Distribute Macro To Others

Feb 28, 2008

I have created a Macro and would like to make it available to other members of my team. This Macro is to be used in various different workbooks - so it cannot be tied to a single specific workbook. I tried creating an Add-In, but I've learned that, even if I instruct my colleagues how to load the Add-In, they will not be able to see the Macro that is inside it. So what methods are recommended for distributing Macro's to other users?

View 7 Replies View Related

How To Distribute 200 Items Into 14 Employees

May 19, 2014

I been working on excel sheet that will distribute 200 items between the employees that are available to work. I've attached a copy of the workbook. First I need to look at the quantity of employees I have working that day. Second Distribute the items to each one of the employees. Third give the first employee the first piece. Fourth give the next available piece to the last person with a zero piece in their list.

book8.zip‎

View 12 Replies View Related

Distribute Data Without Repetition

Feb 17, 2014

I organize monthly audits of work completed by the team. Essentially we all audit each others work; but we cannot audit our own work.

I would like a formula that will evenly distribute the jobs for audit without passing a job back to someone that owns the job originally. e.g. Gareth cannot audit a job Gareth owns, but can audit Zach or Claire's jobs. The Excel sheet attached should make it easy to understand.

So far I have tried: =INDEX(A2:A24,RANDBETWEEN(2,24)) but this duplicates data and I cannot get it to evenly spread the jobs amongst the team.

View 3 Replies View Related

SUM Spread / Distribute Formula?

Dec 29, 2009

I've enclosed a sample summary page to see how I'm going to use the formula.

View 4 Replies View Related

Password Protection & Distribute

Dec 15, 2009

Is there a tool that can make an Excel application behave like a real software? Meaning, the user will still be able to do what I planned for him to do with the file, BUT it will be very difficult for the more-than-average user to crack any password protections I have applied to the file? (Something like an MDE file in Access, perhaps?) The reason I am asking is because I have invested a lot of time and efforts into creating an application that I wish to sell to some of my clients, and dont want them to easily find their way into the formulas and macros I have placed in the file.

The protection tool does not have to be an Excel tool, necessarily, but it should not require any programming skills (because I dont have them. If there isnt such a tool? My application contains User Forms (for input), a Database (with Auto Filters and formulas) and some Pivot Table reports. BTW, my application was created in Excel 2003 but should also be used by users with Excel 2007.

View 2 Replies View Related

Formulas That Distribute Days Across Years?

May 16, 2014

I need to create variables that splits out a length of stay across years and enters the number of days in each of those years into separate year variables. Below are two sample cases:

CaseID StartDate EndDate
222 12/23/1988 01/01/1997
22112/25/1989 2/12/1990

After creating different variables for the different years, in this case: 1988, 1989, 1990, 1991, 1992, 1993, 1994, 1995, 1996, 1997, I want to be able to pull out the number of days that goes into those years as separate variables.

Thus

caseID daysin1988 daysin1989 daysin1990 daysin1991 . . . . 1997
222 9 365.25 365.25 365.25 0
221 742

and so on

View 3 Replies View Related

Distribute Work On Daily Basis

Jun 16, 2014

Attached a work tracker here for your ref.

I this, We are distributing work on 6 hrs/day basis. What I need is, I need to automate the work distribution on daily basis and should show the hrs also. and the projected date of completion. (yellow highlighted cells are where the formulas are needed)

How can we do that? Excel Help-Updated.xlsx

View 5 Replies View Related

Formulas To Distribute Ranked Items?

Jan 7, 2014

Our Favorite Gifts.xlsx

This is a simplification of what I really need. But say I have 5 people, and I have 5 gifts (A-E) Each person is in order by who gets first choice, second, etc. And each person has indicated from 1 - 5 which gift they like best. 1=most favorite 5=least favorite

I'm trying to create formulas to show who gets what. My actual spreadsheet has more people/items but is virtually the same thing.

In the attached example,the first person, Bill, gets first choice, then Mary, then Karl etc.
Bill would get Gift C. Mary would get gift A Karl would get Gift D because gift C and A are already taken.

The spreadsheet itself could be done differently if there's a easier/better way to get the data.

View 10 Replies View Related

Formula To Distribute A Value Along A Bell Curve.

Nov 19, 2008

I'm trying to find a formula that would spread out a value amongst multiple cells along a bell curve. Say I have $x,xxx. I want to spread that amount out in a certain proportion, regardless of how many cells I'm spreading it throughout.

Say I have a $10,000 project. I want to be able to spread it out like a bell curve in the cells I choose. For instance, if it was a five month project it would be distributed as

January - $1,000
February - $2,500
March - $3,000
April - $2,500
May - $1,000

If I added another month it would change to
January - $833.33
February - $1,666.66
March - $2,500.00
April - $2,500.00
May - $1,666.66
June - $833.33

I already thought of manually adding percentages to each cell, but the formula needs to be dynamic.

View 5 Replies View Related

Distribute Duration Evenly Amongst Tasks?

Sep 10, 2013

I'm working on a project that will will have varying tasks & estimating the total duration to complete these tasks.

For example:

If estimating that the min-project will take 90 days & I have 17 tasks, how can I distribute the 90 days evenly amongst the 17 tasks?

View 5 Replies View Related

Evenly Distribute Hours Over Month

Dec 9, 2006

I am currently tracking our company's service tasks in Excel and would like a formula or method that will take the total hours a task has been approved for and equally distribute the hours into columns under each month.

Column A = Resource
Column B = Task Owner
Column C = Task Name
Column D = Task Description
Column E = Approved Hours
Column F = Start Date
Column G = End Date
Column H = Jan 07
Column I = Feb 07
Column J = March 07 etc. etc.

I basically want to take the approved hours (Column E) and have those hours automatically calculated and filled into the month columns (Column H forward), as per the start and end date fields (Column F & G) so the hours are evenly distributed over each month.

View 9 Replies View Related

Distribute Quantities From Known Average Price

Dec 10, 2006

I'm having some problems to find a way to distribute quantities from a known average price. Here's the Example:

My Client bought 100.000 Shares from a Stock, in various prices.

Qtty Price
10041,71
30041,61
70041,78
1.50041,72
4.40041,68
5.50041,75
10.00041,94
13.50041,70
64.00041,80

Then the client wants to distribute this trades for 7 different funds at the same average price.

Qtty Fund 1: 294
Qtty Fund 2: 541
Qtty Fund 3: 1.213
Qtty Fund 4: 13.370
Qtty Fund 5: 16.582
Qtty Fund 6: 28.002
Qtty Fund 7: 39.998

I'm sending an example attached.

View 4 Replies View Related

Distribute Workbook With Calendar Control

May 16, 2008

I would like to distribute a workbook that makes use of the calendar control in a userform.

Trouble is.... Not all the end users have the control installed or even a copy of the mscal.osc file.

This workbook is going to be rolled out to a large number of users with varying levels of computer literacy. For this reason I would like to try to 'attach' the calendar control to the workbook OR somehow distribute the mscal.osc fil and automate the installation.

View 4 Replies View Related

Distribute Project Hours Automatically Across Weeks

Mar 27, 2013

How to achieve the same as in the linked thread below, but in weeks instead of months? I'm very insecure about how excel deals with weeks.

[URL] .....

View 1 Replies View Related

Create VBA To Distribute Rates And Exclude Weekend

Jun 24, 2014

I have to create VBA according to these criterias. I have a list of 22 rates that i need to distribute by order for a month period (30 days) noting that I need to skip weekends (Friday and Saturday)

Day 123.06.2014Monday20%
Day 224.06.2014Tuesday10%
Day 325.06.2014Wednesday7%

[Code]....

create this formula so that each time I change the date the rates are distributes accordingly

View 4 Replies View Related

Distribute Values To Cells Depending On The Input Value

Feb 25, 2009

I would like to distribute values in cells depending on the input value (excel file attached). This file is a test and basically i've been entering the data manualy. The format is flexible, so it can be reorganisaed.

View 2 Replies View Related

Distribute Data From One Main Worksheet To Several Other Worksheets

Jan 6, 2010

I am in need of an Excel Macro to help me create an address list by market. For simplicity sake Ill set up a mini-example: I have an Excel document with a MASTER tab that lists the Market (Column A), a persons name (Column B) and their Phone number (Column C). What I need to macro to do is read column A and make a new tab for each market in that column, then populate the name and phone fields accordingly.

A found one macro that was close, but it does not automatically make a new tab (I have to do that manually) and it does not refresh the sheet each time I open it, so when I run the macro again I get duplicate entries.

View 13 Replies View Related







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