I need to mine a huge spreadsheet of about 30,000 cells and make columns from specific words. As in.
I need to search for all words "table" even if the word is "My_table" or "aTable" and suck them into one column. I have to do this one several words.
Let me know if you have the solution. I am trying to write something but I am unsure of the syntax at this level.
I have data written in Cells B5, C5, D5 etc that changes as you add more column data
I am trying to figure out how to merge cells starting from Cell B3 based on used cells in row B5 to end of column.
I'm trying to use the .Find and .FindNext functions to find how many cells in Worksheet("WAS") have the same value as the ActiveCell (B3 in this case) on the Worksheet("DDS"). Basically i'm just trying to figure out how many times this sub goes through the Do While loop. However, "tick" keeps coming back as a value of 1. I know there's something I must be doing wrong or something i'm not allowed to do but i'm still pretty new at this.
View 2 Replies View Related Now I know that merging cells is usually not a good thing in VBA, but I'm working on a data report that just looks bad unless longer pieces of text can span several cells to minimize column width.
So here's the problem:
I have a column of 1's and 0's in columns E and F, and text in Column G. I want to merge the cells in columns G and H for that specific row if there is a 1 in either the E or F column for that specific row. And I need to do this for a long range...rows 7-5000. Any ways that VBA can do this?
I have a list of values, some are like values, others are not. is there a way to have the column to the right automatically merge the cells which have like information to their left. Attached is a sample file. I want to be able to have the cells in column B automatically merge based on the values in column A. I'm not sure if this can be done, but figured I would ask before I spend hours merging cells.
View 2 Replies View RelatedI have a huge document that looks like this
Column A______Column B_____Column C
100/12__________B___________$
100/12______________________@
100/12______________________€
250/13______________________€
250/13______________________$
I want to keep in ColumnA all three rows of 100/12, because it has a value in Column B in one cell-which is the criteria, and remove the 250/13 because it has no value in cell B.
I was assuming that merging duplicates in column A, and than remove empty from ColumnB.
I need to concatenate varying numbers of cells based on duplicates found in a separate column, but I'm not sure how to approach it. I have 41,000+ rows of data, so I have to find a formula.
Example:
1AB2Denton, PaulFB357D4D3OwensTest, MarcyFB539F934Brennan,
JosephFB539F935Bowser, AmyFB539F936LaRock, ChuckFB667D3B
Based on duplicates in column B, I want to combine the data in column A into one cell. The duplicates in column B could be only 2, or could be 20+.
I am trying to select a range based on two variables which store the column numbers. what I have is:
View 4 Replies View RelatedWhat I want to do is Average out a range of cells "F4:F343". That's the easy part =average(F4:F343). However I want to exclude any numbers for that average if data in coloumn "Q" = "FMC"
Would that look anything like:
=average(F4:F343)/countif(Q4:Q343, "<>FMC")
I'm also displaying it as minutes in a day so i added *24*60 to the end of the formula and formatted to Number/.00. I've entered the formula and it will calculate but when i enter FMC to the appropriate area the average wont change.
I have 2 columns of data and want to be able to merge as follows
COL A COL B
4123567 123.45
4125467 900.56
4356456 456.32
need to get result of
COL a
4123567
123.45
4125467
900.56
4356456
456.32
I'm looking for a function (I'm thinking 'SUMIF') that will search through column A of Sheet 1 and add column B of Sheet 1 when the cell in column A contains one of the strings listed in column A of Sheet 2. I can only find information when searching for single strings or cells.
View 8 Replies View RelatedI'd like to merge (and position in the center) all identical rows within column A without affecting the columns to the right of it. I have attached a small sample of what I'd like to accomplish.
View 5 Replies View RelatedI'm writing some code where I need to know the column in which a specifc text string occurs. Because of the nature of the sheet, the string will only appear in one column, but that could be in almost any column on the worksheet.
For example say the text string is "Year 1", I need to find the column with this string, and then offset down to a predetermined row, or offset over to the appropriate column (something like below).
With Selection
.Offset(0, x).Select
If List.Selected(0) = True Then
y = i - 1
With Selection
.Value = Salary * Inflation1 ^ y
End With
Else
Dim ColNo As Byte
Dim LastCol As Byte
ColNo = Application.WorksheetFunction.Match("Column find", Range("sheet1!A1:Z1"), 0)
LastCol = Range("sheet1!b1").End(xlToRight).Column
But the below code errors?
Dim ColNo As Byte
Dim LastCol As Byte
ColNo = Application.WorksheetFunction.Match("Column find", Range("sheet1!A1:Z1"), 0)
LastCol = Range(Cells(1, ColNo)).End(xlToRight).Column
Please see the attached sheet. I have columns B through a lot (B through O in my oversimplified example). In every 7th row in each of these columns there is either a 1 or a blank/zero. I need to multiply that 7th number by the Quantity in column A, to achieve a total (ie the sum of each result of 7th cell*quantity) for each column in the bottom row, labeled "Totals".
In the actual version of my sheet, there are far too many rows to select everything manually. I've been fiddling with combinations of COUNTIF/COUNTA and OFFSET, but I haven't come up with a way to check for the 1 in every 7th row, THEN multiply that 1 by the quantity in column A, THEN add up the results for each column. As you can see, there are 1's elsewhere in the columns that are irrelevant to this particular calculation, so something like LOOKUP would also have to look in every 7th cell and couldn't just look at the column as a whole.
If you can't provide an immediate solution, but can at least point me to a resource that would allow me to devise a way to isolate every 7th row (THAT part is the sticking point), I'll surely post the solution to my own thread with updated keywords if I need it.
I'd like a macro that does 3 things..
1. Find the last row (cell) of data in the "Customer Number" column. This search should be by the name "Customer Number" rather than by column letter because the column that "Customer Number" will be in can change.
2. Find the column named "Purple" (also by name for same reason)
3. If the "Purple" column has no blank cells in those same number of rows as the "Customer Number" column, delete the whole "Purple" column.
Looking to find 1 of 2 words in a cell in column B and return the word found in the same row in column E. This seemed easy but I am not having any luck.
the cells in column B have several words in them but I am looking for 2 specific words "PLAT" and "ORIG". If the word is not in the cell, it should show a blank cell in column E in the same row, otherwise one of the 2 words should be in that row in column E. A VBA loop would be ideal but a formula that can do it might work as well.
I am trying to clean up and simplify my macros by compartmentalizing frequently repeated commands. I like to search for text in column headings and define the cell containing it as a range that I can later use .column or .row commands on, so my macros are a bit more flexible. The code I currently use for this is
Dim XColumn As Range ‘the cell containing the text “X”
Cells.Select
Selection.Find(What:="X", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Set XColumn = ActiveCell
I would like to be able to create a Macro (possibly name FindSetCell()) that will accept arguments and achieve the results of the above code in other macros
I think the command might look something like FindSetCell(“text to be searched for”,”name of newly Defined Range”) But I have not figured out how to code it
Very simple program I think, can either be solved by build-in functions or macro. So situation is I have a table, where the D column, contains certain words.
Now I have table where the M1:M10 column contains the same words and the corresponding column (N) contains the value.
So for example , cell D5= "A" and I find that cell M6 is also "A" so I then go at cell N6 which has the value "3.3". So now I want I5 to have the value 3.3 in it.
In Summary, I want the value of the n column copied into column I. Now I have plenty of rows in the D column so I prefer a fast way.
i want to find all the empty cells in column M & N within the data range and input formula into them. How can i make it to input the formula only within the data range (maybe can take reference to data in column E, only when there is data in column E then empty cells in M & N will input with formula)
View 7 Replies View RelatedI have 2 worksheets, "Data Dump" and "Target List"
I'm trying to cycle through the "Target List" in column "A", find the same value in column "B" in the "Data Dump" sheet. When I find it, I want to copy several other cells from the found row into cells on the "Target List" (though probably to a different column). Here is the code I'm trying to use. How to correct it to get the result I'm looking for.
[Code] ....
I have a excel which contains 5 columns in which 5th column data cells has to be replaced with another set of data which have relationship with other 4 columns data.
View 3 Replies View RelatedI am manually scrolling through Excel right now to find all blank cells in column L. Some cells have dates, others are blank. IF L is BLANK, I go to row K and copy/paste row K to row L. IF K is BLANK, I got to row I and copy/paste to row L.
Is there a statement I can put into row L to basically say "If blank copy/paste from row K. If row L and K are blank go to row I and copy/paste to L." ???
I want to find a content of ActiveCell only in column "C", beginning from cell C4.What must be in Look in, and Look at?How to accomplish to match whole number,ex. "1" in cell with "1",no to find "1" in "123"
This function searches in whole sheet,but i need only in C column!
Cells.Find(What:=ActiveCell, After:=[c4], LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
I have 3 columns containing id, item, colour. I want to check for duplicate id's in the id column and where duplicates are found merge the colours into one cell, as follows:
iditemcolour
1245bookred
1245bookorange
1245bookblue
1456chairred
1367chairgreen
1876tablewhite
1876tablebrown
would become
iditemcolour
1245bookred,orange,blue
1456chairred
1367chairgreen
1876tablewhite,brown
In the attached sheet I need to check on the Destination in column B, and if it matches set criteria, divide the figure in Column E by 2. eg If LAX is the destination, then for any rows directly under LAX until the next destination appears, column E needs to equal column D divided by 2. If the Destination is not LAX, then Column E equals Column D.
the difficulty I am having is that there can be a variety of row numbers for each destination. I am having trouble creating a formula that can basically work backwards from Row-1 in column B until it gets to a text string, and check that text string against the criteria. so it may be row -1 up to about row -9 dependant on the number of flight times available to each destination throughout the week.
I have many rows of data. How can I find duplicate cells in a column?
View 9 Replies View RelatedHow can I merge cells in the same row so when I sort a column (A>Z for example) the contents of the row near the selected one change position as well. I paste a picture for example.
View 9 Replies View RelatedI've got a workbook with a ton of worksheets in it. Every worksheet has 4 columns a, b, c & d that I want to merge so that column a will be the contents of the 4 cells above seperated by _ so it will become a_b_c_d. not sure if im explaining this right but i think you'll get what i mean. i need to do that for every worksheet in the workbook
View 5 Replies View RelatedI say reluctantly because it seemed easy enough that I should have been able to locate it in the help file of VBA. Which leads me to believe I do not know how to search effectively in VBA, yet/still.
how to merge cells w/ the use of VBA.
Below is my Post and Answer by EE ....