Iterating And Transfer Data
Jul 11, 2006
I want to check 3 columns of data in one worksheet where all 3 criteria have to be met, and where the 3 criteria are met I want to put those details onto a list in another worksheet. Example:
A B C
1 Red Bag 1
2 Blue Shoe Complete
3 Green Bag Complete
I have used SUMPRODUCT after advice from this site to find the line where all 3 columns meet the criteria if for example I wanted to search which lines have "Red" and "Bag" and "1", 1 being a code for missing, in this case, paperwork. What I want to do is check column C on all lines to find a 1, and any that are found I want to list A and B for that line on a separate worksheet to create a report of current outstanding paperwork
View 6 Replies
ADVERTISEMENT
Oct 28, 2011
I have four variables a1, a2, cr and s in the macro below.
Code:
Public Const cr As Long = 4
Sub Mode5()
'4 are the constants you can vary to test different scenarios
Const a1 As Single = 0.1
Const a2 As Single = 0.4
Const s As Single = 3
It is easier if think of these variables in this format (a1, b1, cr, s).
So taking the code above as an example they can be written as (0.1, 0.4, 4, 3).
Ultimately I would like to try different combinations of these values to get a desired outcome. Currently I change the values manually each time.
Variable a can range from 0.1 - 2.5 (25 numbers)
Variable b can range from 0.1 - 2.5 (25 numbers)
Variable s can range from 1 - 10 (10 numbers)
Variable s can range from 1 -10 (10 numbers)
write some code to iterate through different combinations of these variables. I presume it would be some sort of loop which increments each variable a certain amount each time until the max value is reached.
For example the first combination would be (0.1, 0.1, 1, 0.1) and the next would be (0.2, 0.1, 1, 0.1) and the next (0.3, 0.1, 1, 0.1) etc and the last one would be (2.5, 2.5, 10, 10).
Altogether there can be 25 x 25 x 10 x 10 = 62,500 combinations.
View 5 Replies
View Related
Aug 14, 2014
I use excel and would like to know how to copy a large volume of address data but at the same time filtering out irrelevant data placed under each other in a row, in this case, air compressors air conditioning web address etc ( see below for example). I need the first 5 lines only. The rows of unwanted data are irregular i.e some have 10 lines, others 5 , and others 2 or one line which makes using a formula difficult as there is no consistency. The data eventually need to be placed horizontally in columns to be compared to other address lists. To make matters worse, the text data has been merged and wrapped.
BDD LIMITED
3 Telford Place
L*****r QLD 4315
Phone: 07 5777 3622
View 14 Replies
View Related
Feb 4, 2010
I have a spread with five tabs. Each tab is a person's job responsibilties for numerous properties. Each person works with a property called Orange Avenue. I want to create a tab for JUST Orange Avenue items but want it to automatically pull any and all info from each tab where that person's Ornage Avenue duties are.
View 9 Replies
View Related
May 17, 2007
cells(a,b):cells(a+4,b) have 1,9,9,0,5
cells(a,b+3):clells(a+4,b+3) have 1,9,0,emtpy,empty.
how to fill 9,5 to empty cells.
View 7 Replies
View Related
Jan 20, 2007
I'm trying to convert this code so it transfers the data without the input box being needed.
Dim response As Long
Dim strLastRow As String
Dim rngC As Range
Dim strToFind As Variant, FirstAddress As String
Dim wSht As Worksheet
Dim rngtest As String
Application.ScreenUpdating = False
Set wSht = Worksheets("Transfer Sheet")
strToFind = Application.InputBox("Enter Your Initials")
If strToFind = False Or strToFind = "" Then Exit Sub
With ActiveSheet.Range("H2:H5000")
Set rngC = .Find(what:=strToFind, LookAt:=xlWhole)
If Not rngC Is Nothing Then
FirstAddress = rngC.Address
Do
strLastRow = Worksheets("Transfer Sheet").Range("A" & Rows.Count).End(xlUp).Row + 1
rngC.EntireRow.Copy wSht.Cells(strLastRow, 1)
Set rngC = .FindNext(rngC)
Loop While Not rngC Is Nothing And rngC.Address <> FirstAddress
End If
End With
View 12 Replies
View Related
Jan 23, 2009
I have to workbooks and I want to populate one of them with data from the other. The receiving document has the fields Account and SubAccount which I need to match up with the account and subaccounts in the other workbook.
However, in the other workbook, the line looks like "1000 Wages 000001" (which is account, name, subaccount). I can't find out how to have my receiving document scanning the providing document and when it finds an account and subaccount (in the above string) that matches the account and subaccount in the receiving document, to bring over the data.
View 2 Replies
View Related
Feb 15, 2009
From the attached spreadsheet I need to transfer all the rows where the issue status in column T is either ongoing or open. Now I know how to transfer them over if row 1,2,3 etc was going into row 1,2,3 etc into the new spreadsheet but as not all rows from this sheet is going over I need to now how to transfer data so in the new spreadsheet it just follows down rather than have spaces in it. i.e. if on this sheet I need to transfer data from rows 1, 5, 10, 15 on the new one they go into row 1,2,3,etc
View 7 Replies
View Related
Apr 4, 2009
There’s one “master table”. Two columns in master table are titled “Name-1” and “Name-2”, and all columns in front of it carry “Properties” corresponding to these names.
In a “second” table, when we choose/type the Name-1 and Name-2, corresponding properties should automatically be picked up from the master table. Is that possible?
View 12 Replies
View Related
May 13, 2013
I have a sheet with 1250 data. All are in Column "A" I want to print them out and therefore would like the data also in Column "B" and "D" in order to save printing paper and make it easier to search. Since the data is alphabetical sorted, doing it manually is a big job.
View 6 Replies
View Related
Sep 26, 2013
I have a new spreadsheet that I made to give us the break down of each invoice we create. what we want is to see each job that is done (by code), the amount we charge per each, the cost of goods per each, and the profit margin. In this spreadsheet I have two tabs. in tab one it has the form I made that has a place to input the data that will change with each invoice as well as a section that is broken down into columns. In these columns I have it listed this way. CA=Job code, CB=Job Description, CC=how many, CD=Charge ea., CE=rate total, CF=COGS ea., CG= COGS total, CH=profit
I have the formula done for all of the math the get the totals. My idea was to make a second tab. In tab 2 I have everything listed in columns that will go into tab one. It is broken down like this. CA=Job Code list, CB=Job Description list, CC= charge ea. CD= COGS ea.
What I want to be able to do is when I type in the job code in the first cell under the column A on tab one, it will bring over all of the info from tab 2 and put it in the right spot for that code. So if I typed in say TT001 in that cell it would bring over the description matching that code and put it in the cell under B, along with the charge in column D, and the COGS under column F. And I want it to be that way for each code I type in and it will be different each time I fill this out. How do I explain this part? Ok so I a new invoice made its # 22. I take this spread sheet and do what I want and get all the totals so I print it out. I then want to be able to clear all the info and or just type in a new set of codes that will be on invoice # 23 and then #24 etc.
So what I am saying is the codes will be different each time I fill it out. that's why I have them listed on a different tab and will just put them in tab one long enough to get the total and print it out. Is that clear as mud? I will stop here and wait and see what questions I get back.
View 7 Replies
View Related
Jan 30, 2014
I have a program I built and need to transfer data from a form, from workbook "C" to another workbook "W". The workbooks are open by different users . 90% of what I locate on the Web pertains to worksheets and what don't, does not work. The folders are stored in my Public Documents.
View 4 Replies
View Related
Mar 19, 2014
How to transfer data from one workbook that's open to another in a macro form. I included two sample data workbooks I have. Sample will have the macro inside it and will already be open for the other portion of the macro that will be running. I already have code that will open up the Sample2 file. I'm trying to take specific data from Sample and paste it into a summary sheet in Sample2. In Sample i will be cycling through roughly 30 sheets to find this data but I only included one because I just need to know the basics on how to make this work and then how to modify it.
Sample2.xlsx Sample.xlsx
View 4 Replies
View Related
Mar 26, 2014
i need the data from columns D & E on the JOKE sheet to be transferred to columns F & G on the sheet called MY PRODUCTS...
you will see that the products may have slightly different names but the product codes (column C on the joke file and column E on the my products file) are always the same.
i want to just click a button and add the data from sheet 2 tab to the columns on sheet 1 tab.
my products.csv
joke.csv
View 1 Replies
View Related
Oct 29, 2008
I'm trying to get my code to search through some information and transfer the data from one sheet to another. Trying to find out why my code isn't working. I keep getting errors...
Here is the code I am having trouble with...
View 7 Replies
View Related
Dec 24, 2008
I need to transfer a large amount data (~2,5 million lines of data) from one spreadsheet to another while making sure the destination file is kept in a certain format.
Spreadsheet "oxno" is the file that needs filling out with data provided to me in spreadsheet "ox"
The value that is the matching criteria will be the ITS account number (Field B in "ox" and field A in "oxno") and the currency (Field B in "oxno" and field G in "ox")
So I want a macro to look in "oxno" and use field the values of field "a" & "b" and search for the them in "ox" in fields "B" & "G". Once it finds a match it should copy the value of field "I" in "ox" and paste into field "n" on "oxno".
The next data to be copied should be what goes into field "O" in "oxno" this data is derived from field "L" or "M" but only when its of numerical value with one non numerical field in it (usually a "/"). Last value that I need copied over is Field "T" on "oxno" this is derived from field "J" or "M".
I realise the potential conflict because two sets of queries will be looking to gather values from field "M" on "ox". That is why I am hoping when we look for value for field "O" in "oxno" we only look for numbers with one non numerical field in it (usually a "/") .
After finishing writing this up, I realise how wordy this has become appologies for that, but hopefully someone will be able to understand what I am looking to achieve and will be able to suggest a solution.
View 14 Replies
View Related
Mar 17, 2009
on the "all data" sheet, there are two rows that have a Y for file missing - these are to be marked, so that they may be focused on finding their file
currently, i am doing as such and then filtering by that column to print out a list, and would like a simplified solution (populate sheet "missing") with just those rows who have missing files - so that another user may easily view such data without having to fondle around with the master list
View 2 Replies
View Related
Apr 11, 2009
I have two worksheets one that has a weekly schedule and sheet2 (daily attendance) where i want to transfer One day at a time but i only need the Job role,employee name and shift for that day, then i need to do the same for each day but i don't want seven sheets. please find attached an example sheet.
View 2 Replies
View Related
Jun 1, 2009
i have the following macro which transfers credit control data from an outstanding tab to a paid tab:
View 9 Replies
View Related
Oct 30, 2009
I am trying to transpher data between two userform.
The Idear, To store information within a list box on a userform and select what list items "information" is to be copyied to another useform's Textbox.
To have the 1st userform with a Text Box and one Button. When the user click the button the userform containing the listbox " information" is displayed. On this form the user can select an item "Information" that they want to be transphered into the other useform's textbox. (any information that is going into the textbox must only be added to the textbox information as a new line)
View 11 Replies
View Related
Dec 22, 2009
i need your help by a code to transfer the data mentioned in ( main ) sheet ( in attached file ) to the ( 8 agents ) sheets which addressed (A,B,C,D,E,F,G,H) ( in same cells for easy using the code ).
View 10 Replies
View Related
Jan 16, 2012
I have a spreadsheet called DATA1 that has code in 3 cells B2, D2, F2.
I want a peace of code that will allow me to transfer this data when I click a macro button (Lets call the button "transfer") to another spreadsheet call SALES1.
to do this it has to open the cell and place the data in the same cells on sheet1 of sales
View 8 Replies
View Related
Jan 20, 2012
I have 3 worksheets- Sheet 1 and Sheet 2 will have data from the customer that I need to transfer in Sheet 3 as a summary. So if 5 rows are filled in Sheet 1 and 6 rows in sheet 2, VBA code that can transfer data from sheet 1 and sheet 2 to sheet 3 all one after other (i.e. have 11 rows total). The current code formula i have just replaces data that was filled in from sheet 1 to sheet 2.
View 7 Replies
View Related
Jun 28, 2012
I am trying to transfer data from two different spreadsheets where their data has common column titles and then transfer it to a Master spreadsheet. These two spreadsheets will have different filenames most of the time. If the first spreadsheet has a new data, then it needs to be transferred to master spreadsheet and when the other shpreadsheet has a new data, it will be added to the mast template as well. The problem is those two spreadsheets always change their filenames. How can I have a macro that will rely on the columns instead where data resides and transfer it to master template?
What I am trying to achieve is like two buckets with water (two spreadsheets wth some common columns) and their water will be transferred to a big water drum (master spreadsheet). For now, what I'm doing is always copy and paste data to the master spreadsheet and it takes me forever to do it one by one.
I don't know if I can attach my spreadsheets here so I only put the link for pictures [URL] ........
This is what I have:
Sub UpdateFromTable1And2()
Dim sFileName(1 To 2) As String ' List of file names
Dim nFile As Long ' Index for file name list
Dim wb As Workbook ' Opened workbook
Dim ws As Worksheet ' Worksheets("Combined") in this workbook
[Code] ..........
View 9 Replies
View Related
Jun 6, 2014
How can I move every row data to another sheet, when the print(moving) button will shown i every row. Problem is that i need write a code and insert button for every row, how to automate this proces. So the main key is that, i can chouse wich row i want to transfer to another sheet.
View 1 Replies
View Related
Jul 25, 2014
I'm transferring data from a PDF to Excel (the PDF link will be at the bottom of this post). Specifically, I need to get addresses from this document, so, three lines of text into four different cells. I don't need to include the state, but I do need to separate city and zipcode; see below for example.
The PDF is set up as a table, but when copying full pages of it into Excel, everything goes to one column. To complicate things further, the pages do not all follow an identical pattern.
Right now, I'm just manually copying and pasting each cell, but I trust the Excel gods (or demons) that there must be a more efficient way to do this.
This is the link to the PDF document: [URL]...
To give an example of what I'm doing, the first row of my spreadsheet reads as follows: Kroger Limited Partnership I d/b/a Kroger, 1617 Delaware Ave, Mccomb, 39648 (commas represent cell separation).
View 2 Replies
View Related
Apr 8, 2008
I have a series of of quesitons but honestly, I think it'll be answered by ONE question.
This is the my request as described below:
Bob, Jim, and Lisa are Project Managers (PM). Each PM has a Project Template Workbook with required info they need to fill out PER job. This Template workbook has a VBA button that submits their info to the PM Master File.
The PM Master file is a seperate workbook that sums up each project as filled out by the Project Template Workbook.
What I need is the code to either copy/paste the values from the template to the master file ensuring that the most recent addition of information goes to the next row and doesn't paste over the existing data.... or ... I need a different approach.
View 9 Replies
View Related
Jul 16, 2008
I would like to transfer data from one tab to another to for a report.
First off I would like to take all the rows that have "O" in column B. But only if the year in coloumn Q or R is this year. (Q is order data so I show all new orders taken this year) (R is despatch date so I show all orders despatched this year)
There will be around 25 orders total
I would then like to add a total to the bottom of this set of data.
Then move 4 rows down and bring all the data that has a status "HP" in column B regardless of any other criteria.
There will be upto about 100 Hot prospects (HP's)
My data is held on tab "BRAND" and I would like to generate the report on "BRAND HP"
My data has a header row in row 1 and holds data from A:T
The data is of ever increasing length.
View 9 Replies
View Related
May 6, 2009
I have two workbooks book1.xls and book2.xls
I want to load data in sheet1 of book2 to sheet1 of book1.
This sheet1 of book2 has a table of 10 rows and 10 columns.
View 9 Replies
View Related
Apr 17, 2006
We have a worksheet entitled 'Data Output' that has the raw data in column B. The data represents temporary input from another worksheet that serves as an intake form. Each person's intake data will temporarily populate column B of the Data Output worksheet. When each intake is complete, I want Excel to take the data in Column B and find the next available empty column in a worksheet called 'database page 1' and populate it with the data starting at row 3 downward. When the last column in database page 1 worksheet is reached, I need for Excel to go to 'database page 2' worksheet and begin populating the next empty column and so on.
Is there anyone within your forum or group that can help me write the VBA code for this and briefly tell me where in Excel to insert the code. I know formulas basically well, but have never used VBA.
View 5 Replies
View Related
