Moving Data Based On Constant

Oct 8, 2011

I have a list of data which which has repeated supplier numbers in column A. For instance

A123
A123
A123
X456
x456
x456
x456

The information in other 13 columns is variable and I need to be able to move all the rows which start with the first supplier number to another sheet which I will rename to that name, then move the second set of supplier numbers to a second sheet etc. So in my above mini example I would have one sheet named A123 with 3 rows of data and another sheet named X456 with 4 rows of data.

The number of supplier codes will be different every day and the number of rows within each supplier number will also vary. Each new datafile that comes in daily creates a new file do I do not have to worry about existing values only what the current file contains. I have used the following code to move data based on a known constant before but dont know how to tell the macro "move all the data while the value stays the same"

Sub DiffOrderNo()
Dim totalrows As Long, row As Long
Cells.Sort Key1:=Range("A1")
totalrows = ActiveSheet.UsedRange.Rows.Count

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

View 3 Replies


ADVERTISEMENT

Constant Variables - Declare Name In Separate UserForm As Constant

May 8, 2012

I have a userForm (Form1) that contains a persons name that I would like to reference in a separate UserForm (Form2). In the separate UserForm (Form2) I need to reference this persons name many times, so I was wondering if there was a was to declare this name in the separate UserForm (Form2) as a constant. Only thing is that a constant, to the best of my knowledge, must be an expression and not a variable. Mainly, I'm trying to avoid declaring the myName variable in each Sub within Form2, which it will be needed for a ton of Sub's.

Code for Form2: Const myName As String = Form1.txtName.Value

View 5 Replies View Related

Constant In Formula To Change Based On Cell Value

Jan 3, 2008

I am creating on the fly an array (2 columns) in which the one row cell is mathematically derived from its neighbor (A left-hand cell). However, when the mathematically derived product exceeds a value, the row neighbor changes to another constant until its neighbor's product exceeds another value, etc. Can I create such an array without resorting to VBA?

View 9 Replies View Related

Calculate Balance To Zero Based On Constant Fix Rate & Withdrawals

Feb 5, 2008

I have a situation where I have to determine the required size of a deposit from which constant withdrawals are to be made until the deposit runs out to ZERO. The deposit earns interest at a fized rate, capitalized monthly after each withdrawal had been made.

In my example case, the target month (The actual cell that needs to have a value of ZERO (when the deposit runs out), or the "Range") is found in cell G16. In another situation, it may be found in cell G19 etc, depending on the situation.

It is this changing of the target cell (the one that needs to be ZERO) that has me stumped. I've been thinking of using VLOOKUP (not trying to lead you ;-)) to determine the actual position of the cell that need to have a value of zero but do not know how to build this into the VBA code of Goalseek. This target cell in the Goalseek code should be the cell in column G opposite the figure 1 in column A.

In my example I simply typed in a figure 1 in column A - in the actual spreadsheet the position of this figure is calculated with a formula and its position moves from situation to situation. If the 1 is in cell A19, the target cell, whose value should be ZERO, will then be cell G19..............

View 2 Replies View Related

Moving Rows Based On Key

Nov 20, 2008

I have data as follows:

id | name
0 | axe
1 | sword
3 | pencil
4 | fist
5 | hammer
7 | gun
9 | rifle

Is it possible to move the rows to the row# shown in the ID?
- e.g. pencil gets moved from row#2 to row#3

View 9 Replies View Related

Moving Shape Based On Cells Value?

Jun 27, 2014

following code is supposed to check whether cell contains a value and based on that either display or hide a shape. The last couple of lines are moving it. What I would need is to check all cells in a row (not only the first one) and if lets say first 1,2,3...10... cells contain a value move the shape all the way to the end of that cell and so on.

VB:
Sub Worksheet()
If Sheets("Text").Range("z6") <> 0 Then
With ActiveSheet.Shapes("Rectangle 8").Fill

[Code].....

View 4 Replies View Related

Moving Average - Based On Last 5 Weeks

Jul 8, 2013

I am trying to do a moving average and running into an issue with the count part of the formula.

Column C (C2 to C54) has the numbers I need to average.
Average is based on last 5 weeks.

My issue is that C23 is blank as we had no data for that week.
Currently the formula is counting 25 entries between C2:C27, there should be 26.

My average should be 31 but I am getting 33.

This is the formula I am using :
=AVERAGE(OFFSET(C2,COUNT($C$2:$C$54)-5,0,5,1))

I have tried using different Counta and ISnumber still not working

I would rather use a formulas then VB code.

View 5 Replies View Related

Moving Rows Based On Input Of Date?

Jun 17, 2014

I am a production manager for a homebuilder and I am trying to create an Excel spreadsheet that will allow me to input a date in a column and have a second column dependant on the date and move as I change the date.

Since no work is done on weekends, I'd like the output column to skip weekends and move the input to the next available weekday. I have conditionally formatted the date to be a red fill as a visual cue but I'm not sure where to go from here.

View 7 Replies View Related

Moving Rows Based On Criteria (loop)

Jan 30, 2013

I was wondering if i can cut & paste rows to a different worksheet ("sheet2") based on a criteria my current loop is as follow need filling in the code below for if then ..

Sub moving()
Dim x As Integer
x = 0
Do While x < 12
x = x + 1
Cells(x, 4) = ""
Do While Cells(x, 4) = "absent"
If Cells(x, 4) = "absent" Then .....

View 9 Replies View Related

Moving And Deleting Entire Rows Between Tabs In Workbook And Moving Them Back If Needed

Sep 23, 2013

I have an excel work book with 6 tabs. I would like to have Excel move an entire row from one tab to another tab (removing the row and inserting it in the other tab). I.e. Example I have a tab with items that are marked as "Open Actions" so if I were to change the drop down to close. Excel would move that entire row of actions to the tab with the "closed actions" and insert into the next available row. Now if someone were to come back at a later date say no it should be reopened than I would change the drop down to open and excel would move that row back to the open actions tab into next available row. I tried a PIVOT table and no good I played with few macro and not.

View 5 Replies View Related

Moving Rows To Anoter Worksheet Based On Certain Criteria..

Feb 11, 2009

I have an excel workbook with 5 sheets, the first called 'data drop sheet' & the others called 'Louise', 'Thor', 'Peter' & 'Steve'. In the data drop sheet, the first row is header data and underneath (A2 onwards) are rows of data that I have cut & pasted in. The first column (A) of this data will be the person's name, e.g. Louise Wilson.

I want to be able to move the rows for each person to their respective sheets, starting from row 2 as the first is a header. So, for example, if 'data drop sheet' cell A2 says Steve Brady, I want it to copy the whole line to sheet 'Steve' row 2 etc.

View 5 Replies View Related

Lookup Moving Dates Based On Alphanumeric Codes

May 30, 2012

I'm currently creating a formula to look up the date based on the alphanumeric codes, my problem is if it crosses the year, say 2011 & 2012, the lookup will be distorted because codes for jan. 1 2011 & jan. 1 2012 is the same. Our alpha numeric code is A01= Jan. 1.

to elaborate further:

wanted to get the ageing days between todays date and item manufactured date:
today: May 30 2012

when the administrator enters the manufacturing date code of "A01" I have a look up of the code that equates A01=Jan 1. which will compute the difference of May 30 2012-Jan. 1 2012 and give me the Age date of 150. Now, if I enter H1, I wanted to have it look up at the 2011 not 2012 of Aug. 1; is there a look up formula that looks up the date already past (current date below), not the future date of under the same database?

View 5 Replies View Related

Moving Rows To Another Worksheet Based On Text In A Cell

Nov 30, 2012

I would like to make a macro that will move rows from one worksheet to another worksheet.

I have an excel spreadsheet with multiple columns and rows, within one of the columns there is some text that refers to SOS or County, I need to move everything that has SOS to another worksheet title UCC SOS and everything that has county to be moved to another worksheet title UCC County. Here is a sampling of the worksheet

Account
Primary Borrower
Corp
Status

[Code]....

View 7 Replies View Related

Adding Formula To Macro Based On Moving Cell?

Jan 14, 2013

I am having difficulty expressing a formula so that if a cell is greater than zero, it adds the value of that cell to a String figure. I have got as far as the following, the 'problem' area is in red.

Sub Share_Sales3()
Dim Prompt As String
Dim Caption As String

[Code].....

View 3 Replies View Related

Excel 2007 :: Moving Data From One Worksheet To Another Keeping Data And Formatting?

May 5, 2014

I am working on a project that has 5 worksheets. I have been able to figure out everything else I need to do but this has me stumped. I have data in Sheet1 A6, that i want to place in Sheet2 A6, Sheet3 A6, Sheet4 A6 and Sheet5 A6 and keep data and formatting(BOLD AND UNDERLINE). So I change Sheet1 A6 and the other 4 sheets change also. I'm using Microsoft Excel 2007.

View 1 Replies View Related

Calculate A 30-day Moving Average Based On The Last X Number Of Entries And Date

Jul 5, 2006

I have a worksheet that has all weekday dates in column 1 and values in column 2. I want to create a 30-day moving average based on the last (non-zero) value in the column 2.

Since every month has a different amount of days, I want it to search the date that has the last value (since I don't get a chance to update it daily) and go back thirsty days from that date and give an average of all the column 2 values skipping and values that are null or zero.

View 13 Replies View Related

Data Validation Rules Not Moving With Data

Feb 24, 2014

creating a spreadsheet for work which is almost working a treat Unfortunately, when a row of data moves from one sheet (Queries) to another (Archive), data validation is lost. Initially I thought I daidn't have it set up on the Archive sheet, but on moving it back to queries (by use of a macro) the validation is still not working. how to keep validation rules WITH data when it is moved please? I will upload my file when I get home from work....network restrictions prevent me doing it here!

View 8 Replies View Related

Moving Rows Of Data Into 1 Row

May 20, 2014

I'm essentially getting a lot of data at the moment, which has a few orders people have made on my website.

Essentially, think order id, address etc and then all the products the customer has ordered.

However, the part which includes what the customer has ordered creates multiple rows of data, with the order ids etc duplicated. What I need to do is consolidate this into 1 row. So to add additional columns instead of rows.

The reason fro this is I want to mail merge the data into an invoice and mail merges work of 1 line of data at a time. I've attached an example, any way to do this?

(Attached to this post / or linked here: [URL] ....)

View 1 Replies View Related

Moving Data To Another Cell

Dec 11, 2009

I have 3 columns Z, AA, AB. The heading for Z1 is “A”, AA1 is “B” and AB1 is “C”
In column Z2:Z2000, there is a mix of A, B’s and C’s. I want A to stay in Z1 column, B’s to goto AA1 and C’s to goto AB1, also I want this added to a macro that I previously created, so everything happens with one push of a button

Now for save, not sure if this is possible or not, if I can have this added to the macro as well that would be great. When I push my macro button, the file saves to “Dec (today’s date) DB (81).xls” The number 81 is the total count of A, B’s C’s, this # will change depending on how may A, B’s and C’s there are. I really hope there is a way of doing all this

BEFORE

A B C
B
A
A
B
C
C
C
A
A
B
C

SHOULD LOOK LIKE THIS

AFTER
A----B-----C
----- B.....

View 4 Replies View Related

Moving A Row Of Data Into One Cell

Dec 13, 2012

Essentially, there are two columns I am dealing with. One is "Sales Rep" and it lists all of the sales reps employed by the company. The other is "Zip Code" and that will list all of the zip codes that sales rep is responsible for.

Now, I have a row of data, all of those zip codes listed out, that each rep is responsible for, but my supervisor wants all of the zip codes listed in one cell, in that second column. Example: (02018, 34098, 16711).

The commas are not necessary, but is there any way to get this done other than manually entering them?

View 6 Replies View Related

Moving Data With VB Code

Jul 24, 2007

I have a workbook with 5 Tabs. One of these tabs is "Completed" (for completed work) The other tabs are names of Managers and the tabs contain information about who is doing what work for the Manager and information about it.

What i would like to do is in column F on every sheet is the "status". I would like when the "status" is changed to completed, to have VB code move that entire record to the Completed tab.

I think its possible i just don't know how to do it.

View 14 Replies View Related

Macro For Moving Data

Sep 1, 2007

I need to move a lot of data from what was originally a txt document into an easily readable form.

I have used Macro's before but only for very basic routines (so please treat me as a bit dumb when it comes to Macro’s).

I have Include Screen shot of the data highlighted in a colored box and the relevant colored cell I need to move it into.

This needs to be repeated many times with data that is consistent in its layout.

View 12 Replies View Related

Moving Matching Data Into The Same Row From Different Ones

Jul 21, 2009

what im basically trying to do here is to move XYZ from column B and 123 from column C to match up with XYZ in column A ....

View 7 Replies View Related

Moving Data Between Two Userforms

Aug 6, 2009

I am trying to do what I have quoted below. In particular,I have the two userforms set up so that I open one and then click a checkbox that opens the second userform. I would like the data I enter into a texbox in the second userform to populate a text box in the first.


Originally Posted by dominicb
Good evening scott92

Sounds like you want to dump the contents of textbox1 into a public variable and force textbox 2 to pick them up from there. You might have a problem deciding exactly when textbox2 is to update - ie what event you're going to hang it from. Are both userforms visible on the screen at the same time? is the updating to take place in real time?

HTH
DominicB

View 5 Replies View Related

Moving Data From Spreadsheet A To B?

Jan 31, 2012

i need to move data from a speadsheet (that is open) to

P:SharedAgentsAdmin TeamAvrils TeamSamuel Kinver-WrigleyEscalations ManagementSIOXTeamNew SIOX beastFOR GRAPH PURPOSES.xls

So all the data in the open workbook is in a sheet called "move sheet".

So first to move:

A1:D13 to the workbook address above in sheet "admin" but it needs to look for the next available row in col. A to paste the data.

then

A15:D27 to the workbook address above in sheet "Outgoing (Cust.)" but it needs to look for the next available row in col. A to paste the data.

View 5 Replies View Related

Moving Data From One Sheet To Another

Jul 11, 2012

What vba code would I use to move data from rows a and b in sheet 1 to sheet 2. preferably with a button if possible.

View 2 Replies View Related

Moving Data From One Workbook To Another

Jan 18, 2014

I have two workbooks and wish to extract data from the first workbook (second tab), and insert certain cell data into a specific cell in the second workbook (first tab).

The first workbook has this info:
SN
SN
Sort
P/N
Removal
Customer 1
Customer 2
Program
Rec Month

ABCD1234
D1234
1234567899
Scheduled
Customer A
Customer B
Block 10
Nov-13

[Code] .......

This is a very large data set. The S/N will appear multiple times. The rest of the data will change with each entry, but there are some exceptions where it may be the same.

The second workbook has this data:
ID
S/N
A
B
P/N
Born-on-Date
Date Rec
Ship Date
Customer 1

123456
ABCD1234
1 thru 14
Yes or No
1234567899
03/10/2007
11/07/2013
11/22/2013
Customer A

[Code] .......

This too is a very large data set. What I need to do is to find the S/N in the first table "based on the month" and replace the entry under Customer 1 with the associated Customer 2 data entry. The VLOOKUP function can't seem to handle the multiple S/N entries.

View 1 Replies View Related

Moving Data In A Database

Mar 15, 2007

I've "lurked" around the board for a few days now, and I've gotta say this is the most helpful excel forum by far. After wading through a tiny chunk of the thousands of threads on this board, though, I am still having trouble with coding a macro that will do what I need it to do. If it affects anything, I'm on Excel 2002 on Windows XP.

I am currently in a project where I have to go through a database, formatted as thus: ...

View 9 Replies View Related

Moving Data To A New Cell.

Apr 12, 2007

I have cut and pasted a large amount of data onto a spreadsheet. The problem is that I have two rows of data that is associated with one another, so lets say A1 has a name and A2 contains that person's age. This process repeats in the same way in A3 and A4 and so on.

I need to move the data from A2 to B1 without using a simple function like =A2 in B1. The reason is that I will need to sort just the Names in column A once I get the the data moved. I am not sure how to do this.

View 3 Replies View Related

Moving Data Between Sheets

Apr 9, 2009

I have one very large piece of data located in one worksheet that I use across ten other worksheets. I simply copy and paste this data into each worksheet each morning after the data has been refreshed. Is there any way to be able to simply have my master sheet update and then get coppied to all of my other sheets?

View 9 Replies View Related







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