I'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 RepliesI have a column in excel sheet of more than 1000 different names. I want to add S/o with each name how can I do that ?
View 4 Replies View RelatedThe first two columns are an example of my data set; I would like a formula for the 3rd column (Length+1) which will give me the length at the next age (i.e the value in the cell below in most cases) unless the following age is 1 in which case I want it to return zero.
Age Length Length+1
1 22 51
2 51 78
3 78 94
4 94 0
1 31 46
2 46 69
3 69 85
4 85 96
5 96 103
6 103 0
1 19 36
2 36 52
3 52 0
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 Replies View RelatedI have two lists, one has 250 items, the other 4500, both contain a String
I need to compare any word within a cell in the short list, against any word within a cell in the long list.
The returned value needs to display both the short list item, as well as the long list item for manual comparison by an analyst.
I am trying to identify the row(s) where a match occurs when there can be multiple occurrences of the match.
A
B
C
1
john brown
Brown
True
2
Cathy Smith
3
Brown excavating company
4
XYZ Corp
5
Brown Advisors Inc.
The first test I ran in cell C1 with the information above was to determine if "Brown" was located in the range of A1:A5 using formula:
=IF(ISNUMBER(MATCH("*"&B1&"*",A1:A5,0))=TRUE,"True","False")
In the case of the word "Brown" it occurs in the range of A1:A5 three times, so the result of the above formula would be "True"
Now what I would like to do is return the locations, in this case the row number(s), where the word brown is contained range of A1:A5 because in the case of the word Brown, it occurs three separate times in the range of A1:A5. I would like the resulting value of the formula in this case to be "1, 3, 5" indicating the word Brown occurs in rows 1, 3, and 5. The formula also needs to work in case there is only one match as well.
I tried using the below array formula:
={MATCH(FALSE,ISERROR(SEARCH(B1,'Working List of Vendors'!$A$1:$A$5)),0)}
But it would only return the first occurrence of the match which in this case would be row 1, or "1"
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.
I have two columns(1 and 2), Column two will for each data set have two common values. For most of the data set where column two have the second common value, i can find it based on column 1, but where column 1 differs, i do not know how to get data based on column two.
See attached, column 1 and 2 data. Column 3 I took out one common value, column 4 I just did a IF function to populate blank spaces with common value, but for row 24 - 26 if does not work because column 1 does not follow same logic. Is there a way to populate a third column with one value(can be either of the two common values in column 2(note there will be multiple data sets)?
doc list.xlsx
I have Column A with file paths from the same main directory.
C:UsersSJohn.AJAXDesktopTimeBills.docx
C:UsersSJohn.AJAXDesktopTimeDChargers hick.docx
C:UsersSJohn.AJAXDesktopTimeDChargers raps.docx
C:UsersSJohn.AJAXDesktopTimeDChargersDodgersstadium.docx
C:UsersSJohn.AJAXDesktopTimeDChargersDodgersstats.docx
I would like to find the last common folder along the entire Column (Time),
remove all to the left, and insert E: in its place. Getting:
E:TimeBills.docx
E:TimeDChargers hick.docx
E:TimeDChargers raps.doc
E:TimeDChargersDodgersstadium.docx
E:TimeDChargersDodgersstats.docx
I have already created a VBA Script for other parts of this project, so I would like to add the script to the end rather than create a separate macro. If there is a formula that can do this I am willing to work with that as well.
I have two lists, let's say:
A B
Eva 1982
Anna 1981
Lisa 1983
John 1980
Steve 1972
...
And
A B
Eva Female
John Male
Lisa Female
...
Can in any way match the above lists automaticly, so the information from List number 2, column B, appears at the correct place in List number 1, Column C? For empty cells, no information is fine.
In column A I have 50,000 cells, each containing 1 to 10 keywords. For example
A1 = "jobs"
A2 = "jobs in milton keynes"
A3 = "it jobs in milton keynes"
A4 = "sales jobs in milton keynes"
A5 = "well paying brickie work in spain"
etc
etc
At first I was trying to find out the most common keywords in column A, and I used the following code to do so
Table 1
Code:
7/4/2011AMWG16=01BLK25K_USA[a3]*042711Date a Rich Black Man By June 232350$0.19 http://www.jyxsaw.com/blackrm/index.php?t202id=2676&t202kw=AMWG16=01_0210-3
7/4/2011AMWG16=01BLK25K_USA[a3]*042711Date a Rich Black Man By June
[Code]....
I've got two tables of information here, if you scroll to the far right of table 1 and look at the left of table two, you'll see a common code like:
AMWG16=01_0211-20
AMWG16=01_0212-16
and these entries will appear multiple times as well
AMWG16=01_0212-16
appears twice and the data is different and will need to be added up
How can I:
1. Add up the data on each table with the common string such as "AMWG16=01_0212-16"
2. Take all the data that has been added up and combine the statistics, for example,
when its all done it should show
AMWG16=01_0212-16
in the first column, and then all the statistics from the two tables in the columns to the right.
I'm guesssing...
Since some entries appear multiple times, you will need to sum up all the unique entries for that value. Say:
AMWG16=01_0211-20
Shows up three times, you'll need to sum up all the values and this needs to be done on both tables
THEN
You can combine the data
I'm familiar w/ vlookup, index, match functions, but I'm not quite sure how to put this all together
I'm applying a filter in a sheet that's something like this, after applying the filter it looks like this (Example Item 999)
Item
Inv. Qty
Avi. Qty
Flag
BOX
999
12
9
N
X1
[code]...
I was trying to get the "Flag" Column value, (inside the filter and it crashed)
Code:
With InventorySheet
.AutoFilterMode = False
LRowOnQ = .Cells(Rows.Count, "Q").End(xlUp).Row
.Range("B1").AutoFilter Field:=2, Criteria1:=Project
.Range("D1").AutoFilter Field:=4, Criteria1:=ContractNumber
.Range("N1").AutoFilter Field:=14, Criteria1:=Code
.Range("Q1").AutoFilter Field:=17, Criteria1:=">0"
Set rangeFilteredInventory = .Range("Q1:Q" & LRowOnQ)
ControlFlag = .Range("L2").Value
End With
After the filter, it always take the same value.
Code:
With InventorySheet
.AutoFilterMode = False
LRowOnQ = .Cells(Rows.Count, "Q").End(xlUp).Row
.Range("B1").AutoFilter Field:=2, Criteria1:=Project
.Range("D1").AutoFilter Field:=4, Criteria1:=ContractNumber
.Range("N1").AutoFilter Field:=14, Criteria1:=Code
.Range("Q1").AutoFilter Field:=17, Criteria1:=">0"
Set rangeFilteredInventory = .Range("Q1:Q" & LRowOnQ)
End With
ControlFlag = InventorySheet.Range("L2").Value
Is there any way to take the most common letter of the result range?
I'm aware there's a average option in subtotal, but I think it's just for numbers.
Code:
With InventorySheet
.AutoFilterMode = False
LRowOnQ = .Cells(Rows.Count, "Q").End(xlUp).Row
.Range("B1").AutoFilter Field:=2, Criteria1:=Project
.Range("D1").AutoFilter Field:=4, Criteria1:=ContractNumber
.Range("N1").AutoFilter Field:=14, Criteria1:=Code
.Range("Q1").AutoFilter Field:=17, Criteria1:=">0"
[code]...
And it doesn't even do the loop.
I have a matrix with info of persons of different cities in consecutive rows, where the
city data is in column B. I would like to eliminate columnb B and divide all persons from
any particular city with 2 new rows, their city name and below a field for every column data.
*The number of employees for city would be variable.
Original data (The columns used go from A to E)
1 City_1 Employee_1 Address_Employee_1 Other data_Employee_1
2 City_1 Employee_2 Address_Employee_2 Other data_Employee_2
3 City_1 Employee_3 Address_Employee_3 Other data_Employee_3
4 City_1 Employee_4 Address_Employee_4 Other data_Employee_4
5 City_2 Employee_1 Address_Employee_1 Other data_Employee_1
6 City_2 Employee_2 Address_Employee_2 Other data_Employee_2 ...
I 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 have a spreadsheet with approx 7000 rows, many of which contain the same item but with flavors and other variations on the end. An example would be:
VB:
A B
10142 6kg of whey bundle With Free protein shaker-Banana
10143 6kg of whey bundle With Free protein shaker-Chocolate
10144 6kg of whey bundle With Free protein shaker-Strawberry
10145 6kg of whey bundle With Free protein shaker-Unflavoured
10010 **Bodybuilding Warehouse Premium Whey Probiotic - 2.2kg
10011 **Bodybuilding Warehouse Premium Whey Probiotic - 2.2kg + FREE Shaker
Would it be possible To create a New column (column C) which would display all common words from row b into the New column Like below?
A B C
10142 6kg of whey bundle With Free protein shaker-Banana 6kg of whey bundle With Free protein shaker
10143 6kg of whey bundle With Free protein shaker-Chocolate 6kg of whey bundle With Free protein shaker
10144 6kg of whey bundle With Free protein shaker-Strawberry 6kg of whey bundle With Free protein shaker
[Code] ....
I've attached a larger sample of our list to get a better idea of different variations that are on the spreadsheet.
I think what we need is something similar to this thread[URL] .....
Sample List.xlsx
I'm using excel 2008 for mac. I've got a spreadsheet with around 4000 rows. The data is organized like this:
|___________URL_________|______EMAIL______|
|Example Domain
|Example Domain
|Example Domain
|Example Domain
|Example Domain
What I'm trying to do is concatenate all of the URLs into one cell so that each email address has only 1 single row with many URLs associated with it. I tried using VLOOKUP(B1, $A$1:$B$4000, 1, FALSE) but this didn't work. I thought it was a long shot anyway. I also tried writing a bash script do parse a csv with this data and create a csv file with the URLs concatenated with no luck. I'm trying to get my data to look like this:
|___________URL_________|______EMAIL______|
|Example Domain
|Example Domain
|Example Domain
|Example Domain
|Example Domain
Where these are 2 adjacent cells.
EDIT: The format didn't turn out like I'd hoped. In the first example, each URL has an associated email address, each in its own row. In the second, it is just one row, with multiple urls in the first cell and an email address in the second cell.
I have two worksheets. One has columns A B C , the second has columns A B C D. Columns A in the two worksheets reflect the same data (serial number) , although one worksheet could have more rows than the other since it gets updated manually daily. I would like to use a macro that would:
1. use columns A's value in worksheet 1 to match a row in worksheet 2 then merge columns from worksheet 1 & 2 and insert into a new worksheet
2. repreat until all rows in worksheet 1 are read
3. save worksheet 3
I have a couple of spreadsheets. all of them have a same or common column called "ID".
The order of the ID columns of the spreadsheets are not in the same order however.
I'd like to see if there is a way that I can merge the spreadsheets to one using the shared ID.
To explain more:
Spreadsheet1 columns are: "ID", "URL", "Location"
Spreadsheet2 columns are: "ID", "Picture"
Spreadsheet3 columns are: "ID", "Address"
As I said copy/past is not an option as the IDs are not in the same order. I need to create a new spreadsheeet and have all "ID", "URL", "Location", "Picture", "Address" in that spreadsheet.
I have an Excel file in which I want to find the numbers which are repeated in several columns (B to L), but there are too many rows to find them looking one by one cell, is there a function which will allow me to find the common values which appear in these columns?
eg.:
A B C
1 3 4
2 1 7
2 4 7
4 1 1
results: 1 & 4
I've been racking my brains trying to find a way of doing this. I have a list (column A in Excel) of over 50,000 organisations and I'd like to know what the most common words used in the names are. Ideally it would great if I could produce a top 10 list of the most common words at the top e.g. Ltd, School or Church with a count in the next column of how times that word it appears
View 5 Replies View RelatedI have 2 spreadsheets of names (~2500 and ~1800) and a bunch of corresponding data continuing down the row. both are structured this same way:
Example:
Row1: LAST, FIRST, data1, data2, data3, etc...
is there a formula which can "check" the larger sheet for duplicate names (a row with exactly the same FIRST and LAST), and then either:
1) delete these rows from the smaller sheet
2) clear the contents of those rows
3) or at least flag them in some way so I can quickly delete them
it would be quite a task to eyeball and remove these rows one-by-one, so i'm wondering if a formula could somehow do it (I don't really know anything about visual basic)
One other piece of information which might be important:
For these rows containing duplicate first & last names between the 2 sheets, the entire row is not a duplicate entry; only the names will match (columns A & B)... The other columns down the row will have different values between sheet1 & sheet2. Not sure if this changes anything....
I am trying to compare multiple column in a worksheet to find
common component in all the columns and what is unique to a particular
column only. And list the results/finding in adj column. What i am
trying to accomplish is something as below.
BEFORE
Sheet1 Sheet2 Sheet3
Column2 Column2 Column2
02-1234-12 07-1234-12 02-1234-12
04-1234-12 03-1234-12 02-1234-12
05-1234-12 02-1234-12 06-1234-34
AFTER
Common to all Unique to sheet1 Unique to Sheet2
02-1234-12 05-1234-12 07-1234-12
Is it possible to take the information below and make one list out of columns F, H and J based on the common information in column C? For example, group all the 18s in a list, followed by the 44s.this is excel 2007
column CColumn Fcolumn Hcolumn J
Option code
18180L12369301/123/54
18180L12369301/123/54
18180L12369301/123/54[code].....
I'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 have two columns of data and what I would like to do is have excel look over one column ( I5-I379) and for each cell that has say 30 in it I want it to return the value from a corresponding column (G5-G379), to form a separate column.......is this possible?
View 9 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.
If I have a column full of data is there a way to copy every nth row to a new column? For example, I may have dates and corresponding days of the week. Can I copy the date of each tuesday (i.e. every 7th row) to a new column?
View 2 Replies View Relatedsince my previous post got a little complex I will make it easier so I can continue puzzling myself.
I have a value "10:00"
and a table:
00:00 08:00
08:01 12:00
12:01 14:00
etc
Now I sue LOOKUP("10:00",A1:B3). This will return "12:00"
But I would not want it to return this VALUE but want it to return ROW or COLUMN. I tried ROW(LOOKUP.....) but this doesn't work.
How do I look it up and then return either B, or 2 (in this example)
Once I have this I combine my 3 way lookup by combining my new found knowledge