Moving Things To The Right (cross Match Up) With A MacrO
May 12, 2009
Sub Moveit2()
Dim Quantity As Range
Dim Cell As Range
Dim Cell2
Dim Breaks As Range
Set Quantity = Range("a2", "a21")
Set Breaks = Range("g1", "s1")
For Each Cell In Quantity
For Each Cell2 In Breaks
If Cell2 = Cell Then
Intersect(Cell2.EntireColumn, Cell.EntireRow) = Cell.Offset(0, 1)
End If
Next Cell2
Next Cell
End Sub
I have this macro to move things to the right. Sort of like a vlookup in a way. However, it runs using one range @ a time. For example it will look up the quantity in A2 versus what's in Row 1 and if they match, data from B2 would be moved into correct column. However, I'd like to run for multiple columns at the offset so I can don't have to keep changing data in the macro. Like want to run for these ranges at the same time as running the first range.
Set Quantity2 = Range("c2", "c21")..........
View 9 Replies
ADVERTISEMENT
Apr 20, 2007
Here's a sample data set. How can i perform a vlookup to populate my forecast columns and have it match BOTH the customer column and the SPFKey columns?
******** ******************** ************************************************************************>Microsoft Excel - Receiving Qtr1 2007.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutL20=
ABCDEFGHIJKL1Business*UnitCustomerSPF_KeyFORECASTACTUALS*2"NBPRO"Sold-to*PartySPF_KeyJanFebMarTtlJanFebMarTtlVariance3NBPROAAR*CORP.883***01*1224NBPROAAR*CORP.933***020**20205NBPROAAR*CORP.934***09**996NBPROAAR*CORP.999***02*1337NBPROAEROTHRUST*CORPORATION6600***01**118NBPROAIR*CHINA*IMPORT*&*EXPORT*CO.*LTD918***0*11229NBPROAIR*CHINA*IMPORT*&*EXPORT*CO.*LTD919***0*112210NBPROAIR*CHINA*IMPORT*&*EXPORT*CO.*LTD933***042422210610611NBPROAIR*CHINA*IMPORT*&*EXPORT*CO.*LTD934***042424112512512NBPROAIR*CHINA*IMPORT*&*EXPORT*CO.*LTD1742***0*1*1113NBPROAIR*CHINA*IMPORT*&*EXPORT*CO.*LTD1793***0*0*0014NBPROAIR*CHINA*IMPORT*&*EXPORT*CO.*LTD1795***01**1115NBPROAIR*INDIA933***036408215815816NBPROAIR*INDIA934***046176512812817NBPROAIR*INDIA1742***0**00018NBPROAIR*INDIA6601***0*1*1119NBPROAIR*INDIA6602***0*1*1120NBPROAIR*INDIA6603***0*0*00Sheet2*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View 9 Replies
View Related
Jan 30, 2008
I have 2 worksheets named sheet1 and need_to_delete that are in the same format: 7 digit number, 5 digit alpha numeric, text, dollar amount. I need to copy every row into sheet2 where there are no matches in the column A of the two source worksheets, and copy every row that does have a match in column A into sheet 3. I also need to keep the rows in their current order.
View 5 Replies
View Related
Jul 17, 2007
I have two sets of data and each set consists of two columns as in the example. The first column is the X value and the second set is the Y value. The X values of each set are similar but not the same. I need to multiply the Y values of the two sets with same X value.
View 9 Replies
View Related
Aug 30, 2007
I am trying to create a project on excel in which there exists four worksheets. I deleted "class 2-4" due to file size but are very similar. My question here is i need a macro to located the number from the "current" column in the Manual Input section in the "Main Page" worksheet. The current number must match column A into the appropriate sheet (depending on class) and then look for the volume in row 5 and then round up to the nearest number. My goal is to input the "proposed" number into the appropriate column and row. This will be done for four different classes. My goal is to develop this table so that i can look for trends.
Eventaully i would like to concatenate multiple proposed values for each cell as the macro will need to add onto what is located in the cell should the value already exist. Code must be a module so it runs when ever data is entered in the current, proposed, and volume cells in the manual input.
View 3 Replies
View Related
Jul 11, 2013
Im trying to cross match two cells from two combobox's,
Allong the top there are test names, and down the side is staff names,
On a user form there are two combobox's one with staff names the other test names
Untitled.jpg
How can i make it increase the number in a cell where the name and test match the combobox's value
I have Attached an example file,
View 2 Replies
View Related
Dec 2, 2009
sheet 2
column C is list of chemicals (to give context)
column A is a list of the publication numbers they apear in
column B is GOING to be the list of publication titles they are in
sheet 3
column A is a complete list of every publication number in our library
column B is the corresponding publication title
what i would like to do is extract the title from sheet 3 and input it into column B sheet 2 corresponding to whatever pub number is listed in column A of sheet 2
the code i have so far is in column b sheet 2 "=INDEX(Sheet3!B:B,MATCH(A2,Sheet3!A:A,0))"
now the problem i have is this works perfectly when only one pub is listed in a cell in column A sheet 2 but when there is more than one pub listed (ie "pub number" next line in cell "another pub number") it gives a N/A result.
is there anyway to call up multiple pub titles from the multiple pub numbers within one cell
View 5 Replies
View Related
Aug 23, 2008
I have 2 reports with the same column headings. I want Excel to compare each one on the common OrderID and then just show me the ones that don't match--either one invoice can't be found on the other, or in the cases where they are found, the invoice amounts do not match (IOW create an exception report).
View 2 Replies
View Related
Jun 24, 2009
I am running some VBA code that works fine, until I specify a filename that contains embedded blanks. Hopefully, the following code and screen captures will demonstrate what is happening. First the code that opens the files ...
View 8 Replies
View Related
Apr 22, 2008
I am trying to drop (paste) in new data in a range in sheet 2 and cross reference a column with a table in sheet three and display all rows of matching instances in sheet 1. Example:
Sheet 3 has
a1 b1
amcdap amber connor
apsdap ashley simpson
sheet 2 has
a1 b1 c1 d1 e1
amcdap 300 400 2:00 9:00
apsdap 500 300 4:00 8:00
capdap 200 300 5:00 9:00
dlsdap 400 300 2:00 8:00
I need to return only rows 1 and 2 to sheet 1. I guess ideally I'd like to drop data in sheet 2, click button.
View 5 Replies
View Related
Feb 21, 2007
My knowledge of Excel is quite basic, so i had little luck searching for this one.
We have a main data sheet in which everything is inputted, but i would like that data to be split up and reported (copied into) in 2 or 3 other sheets, depending on a single criteria.
Is this able to be done so these 'report' sheet are constantly updated, and the columns in the reports dont include irrelavant columns which are in the main data sheet.
View 14 Replies
View Related
Aug 10, 2014
I am trying to monitor my turnover and taxes but struggling slightly to get things to work. I have attached a spreadsheet that has comments inserted to show what it should be doing, I just can't get that to happen at the moment.
OzGrid help.xlsx
View 5 Replies
View Related
Nov 3, 2008
I have a few excel calendars.
I was wondering can I take a calendar and then put hyperlinks or vba to make a sheet if clicked on.
So If I click on an icon on a date, it will copy a certain template sheet, then link it to that date and that icon.
See an excel calendar, I have a few others to play with aw well.
But this one gets updated on the same the page and not sure how the links/icon would follow the dates. I was hoping for maybe 3-4 different icons on a date.
View 7 Replies
View Related
Aug 29, 2008
I am trying to compare items on two spreadsheet. I have an existing spreadsheet that lists items and then I import another spreadsheet from a database to confirm if new items have been added that were not on the original spreadsheet. I am about to go crazy and cannot figure out why I keep getting N/A (I know there are some repeats).
I have tried formatting, adding in isna, making sure there are no spaces, and paste special as text... still doesn't work. There are hypens in the data so I have removed them with substitute.
View 15 Replies
View Related
Dec 22, 2013
I have to use data tables to calculate Net Present Value (NPV) by altering specific variables.
My NPV is the result of a series of calculations to ultimately arrive at a cash flow figure and then apply the WACC (average cost of capital)
I understand how to use data tables But for the life of me i cant figure out how im going to tell excel that im changing for example wage costs and not fixed assets when trying to asses the NPV sensitivity
My sheet and calculations are all set up
I've done it manually by altering the figures on the Inputs and Data Sheet.
My NPV calculation is on the Workings and calculations sheet.
The data tables i have done manually are on the Inputs and Data sheet.
View 1 Replies
View Related
Feb 25, 2013
I just want to remove all the parts after /p1/ and /p2/ and make each cell contains only the top level data. How do I do that?
View 2 Replies
View Related
Aug 20, 2009
I am trying to develop a point system to rank things. For eg: in the below chart, I have the follow criteria.
In a particular Cell A1, i enter a numerical value like 1.5. Then Cell A2 (Points) should show that the Points earned is 80.
If MTTR is 1.00, then the Points received by the candidate is 100. Similarly if 3.5 or higher is there, then the points should be 0
MTTRPoints
1.00100
1.2590
1.5080
1.7570
2.0060
2.2550
2.5040
2.7530
3.0020
3.2510
3.500
This is only one type of calculation. For other tables, i will have to change the criteria.
Is there any way i can set the limits for A2 as 0 to 100 and based on what appears in A1, the cell A2 should change automatically
View 9 Replies
View Related
Jul 18, 2006
I have attached an excel spreadsheet
how to set a macro to extract the following datas from it.
Game Title
Description
URL
Picture can be set at 1 side to the right of the URL
View 3 Replies
View Related
Mar 12, 2013
So I have this assignments. Its about rental services. I need to create a formula where the price is automatically found from a table depending on what drop down menus are selected. One menu is the rental name and the other is the season i.e. high or low, therefore each rental has two prices.
I know i can use Vlookup if there was one drop down menu but how to do the other. I was think it will interms of if statement but i dont know...
Here is the link to the worksheet: [URL] ...........
View 5 Replies
View Related
Aug 2, 2013
I use MS Excel 2007 and I was wondering if it was possible to enter data for certain things once, and have that data copied automatically every time I type the text. For example, if I type Blue Car in a cell and type 4 in the next cell, is there a way I can program Excel to enter that data every time I type Blue Car?
View 14 Replies
View Related
Jul 16, 2009
I see that IF statements can only be nested 7 times. How do I create a cell that checks to see what item was selected in the dropdown box of 20 different choices and choose a particular cell range depending on what is chosen?
View 9 Replies
View Related
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
Sep 21, 2013
I have a WB with multiple sheets. I have a Feed sheet that automatically pulls data from the web. I have a Scores sheet that currently has all head to head matchups each week for an entire season. So I want to check the cells on the Feed! sheet to find a partial match and if partial match exists then copy cell that contents score from feed sheet to the score sheet next to the appropriate teams name. Currently I have to manually enter all scores each week for the rest of my WB to update.
I need to look at Cell A3 (on the Feed sheet) which has "New York Jets" then search for a partial/similar match on the score Sheet (which is NY JETS, in this case). NY JETS could be in column B (rangeB2:B257) OR column D (rangeD2:D257) BUT I need to search by row, not column, then once a match is found check the cell to the immediate right and only if the cell is blank copy data from the (!feed) sheet to that blank cell on the (!scores) sheet
This is what happens if working right =
look at cell A3 on the (!feed) sheet = "New York Jets" then search (!scores) sheet Column B and Column D by row for a partial match, finds "NY JETS" as match in cell D8, if cell E8 is blank then copies cell H3 from (!feed) sheet, and pastes to cell E8 on the (!scores) sheet, if cell is not blank continues search until 1st blank cell to the right of matching cell is found (as there will be mutiple matching cells with blank cells to the right but I am only interested in the 1st blank cell found, once found and data copied the process is done and then starts over with cell A4)
Look at cell A4 (!feed)= "New England Patriots" then search (!scores) sheet Column B and Column D by row, finds "NEW ENGLAND" as match in cell B8, if cell C8 is blank then copies cell H4 (!feed) sheet, and paste to cell C8 (!scores)
Once this is done it moves on to the next cell in the next row on the (!feed) sheet, A5, to find a partial match for the data in that cell. I need to continue the search for each cell A3 to A74, and if no match is found to move on to the next cell A6... (based on the way the data is pulled in from the web there are some blank cells as well as some cells that say Game Final, this data won't be on the scores sheets in column B or D).
View 1 Replies
View Related
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
Jun 6, 2014
Not sure how to say that, but I'm trying to write a simple macro to copy some data and pastespecial (Transpose) it somewhere else on the same sheet. But I don't want to see the data "moving".
Here is an example:
Code:
Sub Macro1()
Set SCOPE = ActiveSheet.Range("D2:G6")
Set THERE = ActiveSheet.Range("M240")
[Code]....
View 2 Replies
View Related
Jun 4, 2007
I have a spreadsheet that is finally working great. I made a custom tool bar with 5 buttons and each tied to a seperate macro. On my laptop ( where I developed it) it works great.
I put the whole sheet on on a thumb drive and moved it to another computer. When I load the sheet on the new computer , the tool bars are there but they error saying something about they can not find it.
I went into each button and changed it to put the macros in this workbook, saved it and when I put it on the other computer I had the same issue. I reloaded the original sheet and it went went back to all open workbooks.
I think what I did is saved them to all workbooks and they must be stored on the original computer. I thought just changing the button link would do it but I guess I am wrong.
My question is how do I get the 5 macros to follow the workbook? I need to move this to another computer in the morning.
View 9 Replies
View Related
Apr 23, 2007
What I need to do is have the macro move down a cell each time the data is pasted -
Sub saleschartnew()
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Range("G3").Select
Selection.Copy
Sheets("Monthly Sales Chart").Select
Range("E10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Entry form").Select
Range("C13").Select
Application.CutCopyMode = False...............
I did search and found this: activesheet.cells(row,Col).offset(0,1).value = " Properties"
However, my knowledge of VBA is basically zero so I am not sure how this would be implemented into the macro.
View 3 Replies
View Related
Nov 29, 2006
I'd like to write some code to do the following: When the first cell of the row contains an "Y", move the entire row to another sheet (in the same file), keeping in mind that this sheet already contains some rows (so add the row on the first empty row in the sheet). Afterwards the original sheet contains no longer any rows in which the first cell is filled with an "Y".
View 4 Replies
View Related
Mar 17, 2014
I have the code below to add my new sheet and give it todays date (coming from the menu sheet). However I can not figure out how to add the before code so it will save the new sheet at the beginning of all of the sheets every time.
[Code] .....
View 7 Replies
View Related
Jun 5, 2009
I have a massive data file with thousands of lines of data. Each line is a pair, for example
View 6 Replies
View Related