i want to search OK7V0T in all cells and return the cell reference beside matching data row
serial 1serial 2serial 3
box-4OK4KCUOK4KI0 OK4LLS
box-4OK60ZDOK62AP OK62QU
box-4OK6TA5OK6TBP OK6TFW
I'm using excel 2003 and using VBA I want to convert data from my original data below.
The first row is just to identify the columns below it.
REFR1,REFERENC2,COMPANYNAME01,CURNTDATE,QUANTITY,ITEMNUMBER,PRICE
71723,121222010,EXAPLECOMPANY,2-13-2013,1.0000,MPG TBS01 TB,65.9900
71723,121222010,EXAPLECOMPANY,2-13-2013,1.0000,MPG TBS01 CH,86.5800
71721,121221142,EXAPLECOMPANY,2-13-2013,1.0000,MPG PB01,75.9900
71718,121234503,EXAPLECOMPANY,2-13-2013,2.0000,MPLC001,146.9900
71718,121234503,EXAPLECOMPANY,2-13-2013,1.0000,MPMC001,120.9900
71720,121238748,EXAPLECOMPANY,2-13-2013,1.0000,MPS007,63.9900
To be converted to the following:
"COMPANYNAME01",
"TOTALITEMS","REFR1","REFERENC2","CURNTDATE","0","0.00","0.00"
"ITEMNUMBER","QUANTITY","PRICE","TOTALPRICE",
"EXAPLECOMPANY",
"2","71723","121222010","2-13-2013","0",,"0.00","0.00"
"MPG TBS01 TB","1.0000","65.9900","65.99",
"MPG TBS01 CH","1.0000","86.5800","86.58",
[Code] ..........
The converted output data above can be within the same document or output to a text file.
I have two work sheets I'm using. Sheet1!A column contains a list of customer names like "Smith, John".
Sheet2!A contains the same list of name with additional information found in the row. For example, you will find "Smith, John" in Sheet2!A3 and John's phone number in Sheet2!B3
In Sheet1!B it to find the associated telephone number based on matching criteria from Sheet1!A and Sheet2!A
For example, Sheet2 has the following data.
[Code] ....
On Sheet1 I have the following names. You'll notice some of the names repeat.
[Code] ......
In column Sheet1!B I would like it to scan column A from sheet2 and display the data show in column B. So it would look like this-
[Code] ....
You'll notice some names repeat but it always displays the correct number from Sheet2.
I am working with a spreadsheet that has our user data; User(First) Name, User(Last) name, Computer Name, MAC address, Phone Number, Seating Position and Network Port Number.
I have let this get horribly out of sync, moved computers around, and moved them while the MAC address column was hidden, As a result, I have a bunch of users listed as being at the wrong computer. I have a second spreadsheet I generated that gives me the logged in user for about 2/3 of my computers, with the computer name(Just 2 columns, 'A' and 'B'. I would like to have Excel match the computer name and then overwrite the user name. For example, if the first computer in my correct user list is DELL-99945ty2, and the user name is "Jimbo Jones", I would like it to search the first Excel doc(The one with all of the user data), and replace the user name with "Jimbo Jones". Is this simple and straightforward?
In my main spreadsheet, the user name is in column B and the computer name is in column G, So I want to use the values from Column B in my second document to find its match in Column G of the first spreadsheet, and then replace the data in Column B with Column A in the first spreadsheet.
I have a cell (A1) that contains text.
That text will match a single value in a reference list (i.e. range of cells e.g. Z1:Z26).
When the match occurs (e.g. A1 = Z16), I want the format of the reference cell (fill color, text color) to apply to the cell of interest (A1).
The reference cells are manually formatted.
I know that I could create a long list of conditional format formulas instead of using the reference, but it seems that there must be some way to tell excel: if the cell value contents are equivalent, the formatting should be as well.
I have the following info in a table:
name | doc date | doc value
a | 2009/01/01 | IN111
b | 2009/02/04 | IN222
c | 2009/02/05 | IN333
a | 2009/01/05 | CN111
d | 2009/03/01 | IN444
b | 2009/03/01 | CN222
a | 2009/04/01 | IN555
Firstly, I need to find the LAST DOCUMENT DATE for "a" where #doc_value starts with IN*** (invoice). Manually, I can see that it would be "2009/04/01", but my spreadsheet runs 6000+ entries. I need a function to do this.
Secondly, I need to find the corresponding #doc_value for that date (in this case, IN555).
I want the script to find if the value entered in the form is matching the values in column 'A' in the database and if it matches then it needs to select the cell as active cell - to populate the form details. And if there is no matching value found, the script needs to select the last empty cell of the column 'A' to populate the data entered in the form.
1) Form has 10 different fields that needs to be filled by the user.
2) Field 1 - is a text box for 'Request #' to be entered by the user.
3) After filling in all the fields - once clicked on OK, the form should search for the the 'request #' entered on the form in the database (Form and the database are in the same workbook).
4) If the 'Reqeust #' in the Column 'A' matches the the 'Request #' entered in the form, then the matching cell should be selected (Activecell -Were the data can be overwritten, with the new entry)
5)If there is no matching 'Request #' found in the database, the script should loop to select the next available blank cell in column 'A'. So that the form data can be entered.
How do I change a formula cell reference based on another cell's reference? I'm building a schedule that looks to a task's trigger and adds days based on that relationship. All entries in column "A" will be text and all cells in "B" will be the simple formula "=A2" or "=A3". Due date is calculated by adding the value in "C" to the preceding date in column "D". In the spreadsheet below, the trigger for "Budget set" is "Specs written" with 3 days added to the previous due date.
________A________________B_____________C_________D
1 Task___________Trigger_____________Days_____Due Date
2 Design begins__Proj OK______________10____10-Jan
3 Specs written__Design begins (A2)____5____15-Jan (D2+C3)
4 Budget set_____Specs written (A3)____3____18-Jan (D3+C4)
If the trigger for A4, "Budget set", changed from A3 to A2, is there a way that the formula that determines the due date in D4 could read the trigger cell reference in B4 so that the value in the corresponding row in column "C" is added in the date column?
In Sheet1 I've got a list of names in column A, then I've got a worksheet for each name in the list. I'd like to fill in column B with information from the worksheet of the same name as in column A.
Quick example...name in column A is Davis, want to fill in column B with a value from cell C3 on worksheet named Davis and do it for the entire list. Data being looked for is in same cell on each worksheet. Tried using a formula and dragging it down the list, problem with that is it doesn't change the sheetname just the cell.
I need to find and match patterns of strings in a column and fetch data from the adjacent column. I've attached a sample workbook with my sample data.
How can I find the appropriate matching pattern and fetch and fill data from the adjacent column from my source table to destination? I tried the string functions available and used SEARCH function to match the pattern and check whether it is available. However, when the pattern is found, how can I fetch the adjacent column ?
My attempt to code a formula using SUBSTITUTE, MID and SEARCH functions. Below is the monster formula I wrote - it works and returns 1 when the pattern is found.
Formula:
I need to return the matching pattern that is found. And with it the corresponding adjacent cell's value.
Attached File : Find_Pattern_Match_and_Fetch.xlsx
Macro: Copy selected cell to another sheet next blank row
I'll just make this simple to understand.
*Again, I have first sheet, named "SUMMARY"
*On Column A, I have list of names
A1: Star
A2: Moon
A3: Sun
A4: Clouds
*these cells corresponds to the Cells of different sheets
A1: Star = E3 of Sheet 1
A2: Moon = E3 of Sheet 2
A3: Sun = E3 of Sheet 3
A4: Clouds = E3 of Sheet 4
this is what I should get:
1. I select the cell A1 as an active cell
2. press the button (with the macro on it)
3. I will automatically go to the sheet where "Star" is located
I'm looking for code to find a copy of the text in cell AH3 in a range AH7:AH100 then delete all copies of the text in that range AH7:AH100 leaving other cells in that range which contain different text intact.
View 3 Replies View RelatedRylo thanks for your help on the other project. Here is step two for the same project.
Basically I'm going to list a step by step list of what needs to be done then attach some sample sets so you can see what I'm talking about.
Step 1 ........
I want to enter a unique ID into an input box which will tell which row that id is available and then it will again ask me which column the cell needs to be highlighted. Once i enter the data, it will then ask me what is the change in data (again via input box). i can then enter the change data and then it stops.
View 1 Replies View RelatedI'm trying to find a way to search a second sheet in a workbook for specific criteria outlined in a first sheet (in my attached example, from A3 downwards within the 'list of search criteria' sheet), and then to copy any secondary data found against a successful search match to the original sheet, transposed against its corresponding matched search term.
As you can see in the example, the search term 'bindi' (A4 in the 'list of search criteria' sheet) appears in the 'data' sheet 3 times - the secondary data for these occurences ('feathery', 'Fibonacci', 'glassy') is copied to the 'bindi' row on the first sheet and is offset with each copy to produce a transposed-esque effect of copy and paste.
If it's any help, there are a maximum of 9 matches for a single search term in the real document.
Thanks in advance for your help... I tried to adapt a previous solution given to me for a similar question but failed miserably. I bow humbly to your expertise!
I have a client list in a table in my excel sheet. I currently enter the clients in a sheet that looks similar to a form. After the clients info is in this Form I have a button with a macro to copy the client info into a table on another sheet. This works perfectly.
Now I have another form that will retrieve the customers info in a click of a button in order to make changes to the client. Ei:I now have a second phone number and I want to update that client's info. What I want to do from here is take the info in Cell B2 (clients full Name), Do a vLookup in the "Saved Clients Table" in a Range within my table range. (Range: Saved Clients and the client's full name is in $A:$A and called "Clients Full Name" and once that name is found I need to select the cell in column C and the corresponding row.
This will allow me to copy the info in my form, Paste it in Transpose to the cell that is selected. (I already have that code)
Here are screenshots of what I am trying to accomplish. [URL] .......
I am trying to copy a row based on the value of a cell.
I have two sheets in my workbook and on sheet 1, I have a part number and a description. On sheet 2, I have part numbers again, but this time I the description is broken up into the format I need.
What I am trying to do is have excel search on sheet 2 for the part numbers, then copy the information that corresponds to the part number into the correct column.
I have tried using Vlookup. But if the part number in row 2 on sheet 1 match the one in row 8 on sheet 2, this will copy over the data from row 2 whereas I need row 8.
If this would be more doable using VBA, that is fine by me. I haven't been able to figure out anything in VBA or in excel formulas up to this point.
I'm building a spreadsheet for a sporting franchise using a pull-down validation menu. Once the opposing team name is selected from the drop down menu, I would like it to display the opposing teams players and their statistics.
On the database worksheet, I have a web query pulling names and statistics from the web. Each player name has his team name in his corresponding row. What function can I use that will search for that team name and report it back to the display sheet? Once I have the player name there, I think I can figure the statistic portion... But I can't seem to figure out how to pull all of the players with the matching team name into my display sheet.
Example:
A1 on worksheet "A" contains a pull down menu with team names "Lightning", "Storm", "Magic", and "Thunder".
A16-A20 on worksheet "B" contains players from team "Lightning"
A21-A25 on worksheet "B" contains players from team "Storm"
A26-A30 on worksheet "B" contains players from team "Magic"
A31-A35 on worksheet "B" contains players from team "Thunder"
B16-B20 on worksheet "B" contains team name "Lightning"
B21-B25 on worksheet "B" contains team name "Storm"
B26-B30 on worksheet "B" contains team name "Magic"
B31-B35 on worksheet "B" contains team name "Thunder"
If I pull down "Lightning" on A1 (worksheet "A"), I want the following to display:..........................
I've got 3 pairs of columns and I need to sort through them and align the cells in columns E&F with those in A&B and C&D. The cells I need to match up are the times (columns A, C and E)
Example - convert this:
A...............................B..........C...............................D.........E...............................F......
BID TIME.....................BID.......ASK TIME....................ASK......TRADE TIME................TRADE
30/07/2014 14:21:04.....6.10.....30/07/2014 14:22:37.....6.13.....30/07/2014 14:21:04.....6.13
30/07/2014 14:21:06.....6.11.....30/07/2014 14:22:54.....6.13.....30/07/2014 14:22:37.....6.13
30/07/2014 14:22:37.....6.11.....30/07/2014 14:22:56.....6.13.....30/07/2014 14:22:54.....6.13
30/07/2014 14:22:54.....6.11.....30/07/2014 14:22:56.....6.14.....30/07/2014 14:22:56.....6.13
30/07/2014 14:22:56.....6.11.....30/07/2014 14:22:59.....6.13.....30/07/2014 14:22:59.....6.13
Into this:
BID TIME.....................BID.......ASK TIME....................ASK......TRADE TIME................TRADE
30/07/2014 14:21:04.....6.10.................................................30/07/2014 14:21:04.....6.13
30/07/2014 14:21:06.....6.11........................................................................................
30/07/2014 14:22:37.....6.11.....30/07/2014 14:22:37.....6.13.....30/07/2014 14:22:37.....6.13
30/07/2014 14:22:54.....6.11.....30/07/2014 14:22:54.....6.13.....30/07/2014 14:22:54.....6.13
30/07/2014 14:22:56.....6.11.....30/07/2014 14:22:56.....6.13.....30/07/2014 14:22:56.....6.13
............................................30/07/2014 14:22:56.....6.14............................................
............................................30/07/2014 14:22:59.....6.13.....30/07/2014 14:22:59.....6.13
I don't know VBA so hopefully there's a way of doing this with a basic Excel function.
I am writing a function that opens up an external excel document, identifies a string in a certain range and then i also want it to return a cell reference for that string including the number of rows beneath the cell that the string was found in.
View 6 Replies View RelatedI have an items list on page 2. On page 1 I want to create a drop down menu in C2 that references the list on page 2. Then when I choose an item, I want the additional information in cells D2 and E2 to come in automatically. The list on page 2 will be expanding daily, and when I expand it I can simply insert a new row so I correctly alphabatize it instead of constantly having to resort it.
With that said....
1. How do you put in the drop down box that references information on a different sheet?
2. What is the best way to reference the information in my ever expanding situation: Index/Match, Vlookup, Lookup, or something I havent mentioned?
I'm having some difficulties in defining my formula. I'm pretty sure that this should be possible.
I have a table containing values and dates. For each change in dates, I should summarize every value for that date. I cannot use the autosummarize function, because I do not want the sum to be beneath the values, rather in a column to the right of the values.
I have created a formula which checks for a change in date: =IF(C15=C16;0;1) where the C column contains the dates. Basically I say that the Index column should contain a 0 if the date is the same as previously registered, 1 if it is a new date.
The index column is used in some conditional formatting rules, so I would like to keep it anyway.
I'm in the leadup to my A2 ICT controlled conditions piece where i have to make an Excel system. My chosen subject for this is to use the school house points system. I'm going to make a system where the results of house matches are inputted and points are awarded accordingly.
I just have a couple of queries after looking at my initial designs.
1. Is it possible to get excel to look through a sheet, find a text reference, then bring up something in a cell located in the same row? For example, for it to locate one of the House team names, then go along the row and find the ammount of house points they have in order to make a total for them. Is this also possible over a number of sheets? (In order to keep a running total on a separate sheet).
E.G. House, Games Played, Wins, Losses, Draws, House Points
2. For the house cross country event they use a different system which differs from everything i have designed. What i basically need to do is be able to use a drop down against each finishing position where you can select one of the four house teams, then it takes the points won for that finishing position and puts them into a table.
(refer to attached workbook)
For what I'd like to do, the attached workbook actually represents 2 or more workbooks. The 'Project-PO Report' tab is a sample of what will be several workbooks/reports that will be generated from the data in 'POTASK'.
The 'CMD_BUTTON' tab represents how I plan to use a command button " IMPORT DATA" to import the data into this workbook, then use "RUN PROJECT-PO-REPORT" to call the MACRO to achieve my objective,as stated below. (Originally,this workbook was about 8mb, so to get this under the size for attachment, I just copied the bare minimum of what I believe would be needed to assist me, and so there's no buttons or macros or dynamic name ranges, as there will be...)
Here's what I want to do: ...
How can I modify the following formula to find the contents of reference cell, for example if the cell reference (G$3) is equal to 9 I want it to use 8 instead.
Code:
IF(ISERROR(INDEX(tra!$E$2:$E$1100,MATCH(G$3,tra!$C$2:$C$1100,0))),"",
INDEX(tra!$E$2:$E$1100,MATCH(G$3,tra!$C$2:$C$1100,0)))
I tried using G$3-1 but this doesn't appear to work.
This is a continuation of thread 182629 [URL] ....
The End User wanted originally to type characters into cell C1 of a worksheet that the macro searched for in another worksheet, copying across every row containing the input.
The responses gave me the following code which worked perfectly:
VB:
With Sheets("PICKLIST")
myFind = "*" & .[c1].Value & "*"
End With
If myFind = "*" & "" & "*" Then Exit Sub
Unfortunately, the user NOW wants a Text Box instead of putting the characters into a cell.
I added a textbox (originally called 'TextBox2' but renamed "Choice") and "Dimed" CHOICE As Range.
Then tried the following code:
VB:
With Sheets("PICKLIST")
myFind = "*" & CHOICE & "*"
End With
But it generates a "RunTime Error 91" - Object variable or With block variable not set.
I am building a Workbook which takes data from SheetA and inserts it into SheetB.
Part of the data is only entered when a positive value exists.
I then do an export from SheetB.
The problem is that I need to get the column number and pass it to the cell reference based on the field name in row 1.
Dim sFindstring As String
Dim rFindcell As range
Dim iR As Integer
Dim iC As Integer
sFindString = " Find this string in the cell"
'Using cells find the findstring
Set rFindCell = Cells.Find(What:=sFindString, After:=[A1], LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
'OK so look here
iR = 3
'I am trying to pick up the column number
iC = rFindCell.Column
rFindCell throws an object or with block variable not set error. Is there some property that I need to set.
My task is to combine two large databases into one spreadsheet by extending the number of columns. The data from each database is 90% matching based on an identification number, however occasionally there are additional rows or unmatched identification numbers that need to be kept for analysis.
When this happens, there needs to be a blank row inserted to represent the missing data in the rest of the corresponding row.
I am having trouble finding a quick way to do this because I have approximately 12,000 rows (and columns up to DV when combined).
for example:
p1
data
data
data
data
p2
data
data
data
data
[Code] .....
needs to become:
p1
data
data
data
data
[Code] .....
I am guessing I will need a macro of sorts, So far I have made one column that tell me if the ID's are matching or not (1 or 0) and if they are not matching (0) I manually insert the rest of the row that is missing or make space for the duplicate data (which needs to remain).
I have a large workbook, with multiple sheets. I would like to be able to select a name from a drop down menu, and have the cell color change for all entries of that name in my other sheets. I can do it manually, but it would be much cleaner and easier to have Excel do it for me. I have looked at other answers here on this forum and know that if it can be done,
View 1 Replies View RelatedVia conditional formatting, I am searching to change the color of a specific word in a cell (not the color of everything in the cell, but only that specific word - and the word repeats in the cell). A function such as =isnumber(search("NOK";A1)) colors all the cell values, which I do not want to do. How i can perform this task?
View 1 Replies View Related