I want a formula to scan column B for a given word. One version of the formula will look for the word "ACAD", the other "GOV". If it shows ACAD anywhere in column B, I want to put ACAD in column D.
View 10 RepliesI've been using a nice colorful spreadsheet to use to schedule rooms for an organization. Visually it has worked out well but now I need to automatically generate a 'report' using that data. So...
I want to scan each row of a table and create another table with that data transformed.
That is, scan each row and whenever I find any text in a cell I want to return that text (the room being used) as well as the row and column header information (who is starting to use that room and on what date). I've attached a jpeg to show the example.
Ideally, when I scan the row and find text to return the headers I'd like the row scan to also find when the fill color changes and return that column header, too as that that would be the date that the room stops being used.
i have a set of words in sheet1 column A, in sheet2 columnA, i copied the words and type the meaning on columnB. in sheet1, i have a command button adjacent to the word in columnA, when i click that button, it will go to sheet2 with the same word beside the button in sheet1 columnA, so that the user may see the meaning of the word in sheet2. is it possible to generate a code like that?
View 9 Replies View RelatedIn column A we have words, like this:
CHAIR
EXCEPT
WORLD
STREET
OUTSIDE
PEOPLE
WINDOW
LETTER
BOOK
In column B we have another list of words:
NIGHT
DREAM
EXPECT
THOUGHT
TEDIOUS
OFFICE
SETTER
LIBRARY
BEFORE
OFFER
I need a formula in C1 which will check all the words from column B and find the ones which are anagrams of some of the words from column A, and then return those words next to them.
(Anagram is the word made up of another word's letters, without repeating or omitting any, for example anagram of SPARK is PARKS).
In the example above, we would have the words: EXCEPT next to EXPECT (in C3), OUTSIDE next to TEDIOUS (C5), and STREET next to SETTER (C7).
I need my macros to search for the word "Cancel" or "Cancelled" in columns "T" and "U". Once found, I need the macros to make that entire row an opaque shading.
There will be other wording in these cells that contain "Cancel" or "Cancelled". Is it possible for the macros to search in the sentence and find the words "Cancel" or "Cancelled"
I started on the code below but am stuck.
On sheet 2 in cell C5 I need it to find it from sheet 1 referring it from the word "Drinks 1" thats in Q1 and so on along the row
View 9 Replies View RelatedI have a excel file with more then 10 sheets..Some of the sheets contains this word in some random cells" #DIV/0! " I want a macro which can find it in every sheet except parent sheet and can remove it.
View 5 Replies View RelatedI would like to have a formula find a specific word in column L and return the sum from column E for the same fund from column C.
I tried using =SUMIF(L:L,"*annual*",E:E) but that gives me the sum of annual for the entire column. I need to be able to specify the fund.
word "annual" also appears in "semi annual" so I need to be able to separate the two.
I want to say if the word 'suitcase' is in column W (any row), place the word 'suitcase' in column Z, same row.
View 2 Replies View RelatedI'm have a simple spread sheet that includes days and dates. I have a countif formula counting the individual days (as in: Monday = 5, Tuesdays = 2, etc...) I want a formula that returns the most frequent day in text format.
View 6 Replies View RelatedI've imported a lot of data and it looks like this:
blahblahblahWord 1 Word 2 Word 3
First, I want to erase the "blahblahblah"
Second, I want Word 2 to be bumped over to the next column and Word 3 to the column after that.
Is there a formula to do this? There's so many rows of data that it will take a long time to do it manually.
I am trying to scan through all files within a directory and all subdirectories to find files where the name begins with certain characters. Below is code that will accomplish this for a certain directory, but I do not know how to look through the subdirectories within the specified directory. So, the issues are:
1. How do I loop through each subdirectory within a specified directory?
2. I am trying to return the file name, path name (separate from the file name), and modified date. I can return the name. I can return the path, but it includes the name. Is there a way to return just the path?
3. What is the code to return the modified date of the file?
I send out 300 or so spreadsheets. The spreadsheet contains lines of products that recipients have on hire. Column L allows to the user to put YES or NO on each line, to state whether it is correct or not. The user then sends this sheet back.
The difficult part for me is I have to open each spreadsheet, and deal with all the instances of NO in column L. It takes almost a day to go through every spreadsheet.
Is it at all possible to have some sort of macro, VBA sript or access front page, that allows me to search all instances of 'NO' in all the spreadsheets which are in 1 designated folder?
Hopefully I am making sense in what Im after.. ideally I'd need a button on a spreadsheet that I click to give me all rows that contain NO in column L.
All the file names will be constant each week. (so each file name will be 01 - Hire List.xls, 02-Hire List.xls, 03-Hire List.xls etc etc).
Would like to know is that possible to scan some information, e.g table or figures, into the excel spreadsheet.
View 5 Replies View RelatedI want to scan the envelopes coming into our postroom and look up the results within excel. I would then if I match the name be able to print out the details e.g. name floor they are located on etc..
View 3 Replies View RelatedThe issue is to find the address (like A1) to the cell that contain specific text string?
I import text from a web page and I want to calculate some numbers from this information. All the information is spread out in 10-15 columns and 20-25 rows, and the specific value I'm looking for is imported into different rows as the dynamic web page changes, but the column is always the same.
The cell next to the left side of my wanted cell always contain the same text, so I thought I could search or scan the area A1 to P25 for this text and then extract the value of the cell on its right hand.
I can't figure out the specific way to write a Do Until where it exits once there is no more text in any further rows.
Basically scan the rows and find the last remaining row. If there is no data after 20 rows then exit the loop.
This is a sample.
A
B
C
D
F
G
H
I
J
I would want it to understand that there is no information after "J" and to exit the loop.
i've created a spreadsheet that will take in scanned files (from a barcode), log into our system, update the files based on certain criteria, then take information from the file and place it in another worksheet for inventory purposes.
what i'm asking is... is there a way i can have excel drop to the next row each time i scan in each file?
i've noticed the output of the scanner is formatted like this: +000000
i've tried using the sendkeys method with the plus sign, but it always moves down THEN enters in the numbers. i'm not sure if there is a way with the Len function... or some variation of that.
i'm also thinking if i could maybe find the .dll for the scanner, maybe i could set up a reference to it, and have it do something like "afterbarcodescan.activecell.offset(1,0).activate".
I have some code that scans each worksheet in a workbook for sheetnames beginning with 'Model'... if the worksheet name matches, it prints only the used pages on that sheet (see code below).
Dim Ws As Worksheet
For Each Ws In Worksheets
If Left(Ws.Name, 5) = "Model" Then
If Ws.Range("A46").Value = "Page 1-1" Then
Ws.PrintOut From:=1, To:=1
ElseIf Ws.Range("A46").Value = "Page 1-2" Then
Ws.PrintOut From:=1, To:=2
End If
End If
Next Ws
This code works... but I would like it to scan the workbook in reverse order...
ie. print from the last matching worksheet first.
Bar codes would contain a part number and a quantity.
822808 / 1604 pcs
1st question: I have scanned them into excel, but how does the next scan get into the next cell? It is currently putting multiple scans into the same cell.
2nd question: Can the data be broken up into separate columns? (e.g. Column A = "Part Number", column B = "QTY") I know about text to columns, but I think I have to get the format right?)
I'm looking for a code that will scan the rows (x) (of which there are 10), and print the data for the corresponding columns (y) (of which there are 42).
View 9 Replies View RelatedIs there a way to scan a document into excel 2003 and be able to keep the current format ? or some way to be able to get it into excel and the easliy put it back to the format I need?
View 2 Replies View RelatedI have an excel program that is supposed to count word instances in a word document. I can't seem to find the right declaration for a word document.
For example to declare a workbook in excel its
Dim wb As Work Book
I've tried
Dim doc As Word.Document
'or
Dim doc As Word.Application
as shown in some of the forum posts, but an error user-type not defined keeps displaying.
Can I make the cursor use only 2 columns? In other words, I know that when I scan barcode data into A1, I have the cursor automatically move to B1. BUT, once I've scanned data into B1, how do I make the cursor automatically move to A2?
View 2 Replies View RelatedI'm looking for an IF function as follow:
IF Column B doesn't have the word NO or NMI or "N/A" then Column A will remain the same. Otherwise, change to column B value.
Column A
375-9323
384-803
267-6491
Column B
D
NO
A
A
NMI
N/A
I've been playing with different Macro but haven't got any luck so far.
Right now the code below reference AI on sheet BOM. However, I need it to be either referenced by being the last filled row on sheet BOM or by having the word "rounded" on row 4 --what which over column on row 4 that has the word "rounded".
Current code:
Range("J2").Select If Sheets("BOM").Range("C4") = "" Then
ActiveCell.FormulaR1C1 = "=BOM!R[3]C[25]"
Else
ActiveCell.FormulaR1C1 = "=BOM!R[4]C[25]"
End If
I have two columns. One column has UPCs - some of which are duplicates. The second column just has number values. I'm trying to add the sum of all of the numbers in column two which are attached to their respective UPC. For example,
COL A///// Col B
11111111111///// 10
00000000000///// 15
11111111111///// 10
11111111111///// 4
00000000000///// 2
So, I need a third and fourth column to give me the total value for a single SKU(col A) of all the values in col B. In this example the Third column would contain the SKU, and the fourth column would contain the sum of all values in column B that are associated with the single SKU in column three. The third and fourth column would look like this:
COL C///// COL D
11111111111///// 24
00000000000///// 17
I use a scanner that initiates a f2 command and trips my macro. I would like to use multiple scanners using a 8 port hub but I am having trouble with a scanner starting my macro and another scanner dumping data into my macro before the first one is finished. This is dropping scanned data into my macros. Is there a way to prevent the scan from accepting data until the macro has completed its cycle.
View 2 Replies View RelatedI have 2 Worksheets in an Excel 2010 Workbook -
Sheet1
Column1: contains the word "dog"
Column2: contains the word "bark"
Sheet2
Column1: contains the sentence "I like dogs a lot."
Column2: is blank
What I need to do is search Sheet2/Column1 for the presence of "dog" and if it's present, populate the word "bark" in Sheet2/Column2 from Sheet1/Column2.
How can I do this?
Here is what I am trying to accomplish.
I have to columns.
I have copied column A from a PDF file. It contains entries like this, all in the same column:
5 0.002HP Direct- 26845 7/1959 Airesearch
This column contains an ID # (5), a description (0.002HP Direct), a part # (26845), a date (7/1959), and a manufacturer (Airesearch). All this info is in one column. This column is 11,000+ entries long.
I have column B which is 45,000+ entries long, and contains ONLY part numbers, like this:
325111
I would like to compare column B to find the matching results from within the text of column A, and put these matches into column C.