I have a list in column 1 that I would like to do an auto find and replace with the list in column two.
column a
big
fast
tough
mean
goofy
column a
"keyword" store
"keyword" stores
"keyword" stores.com
"Keyword" department store
store "keyword"
stores "Keyword"
I am creating a template for formatting data that I download from a wholesaler to upload to a platform. The large database of around 1300 records that comes with category and subcategory names, among all the rest of the data, that I download There are more column titles and information than I need, so my template will have blank headings that I will know to simply delete after I paste. Maybe there is a Macro for that too.
The category and subcategory names to appear in certain columns. I want those names to appear in multiple columns within the same records. How to create a formula/macro to copy all of the data in one column to a different column with a different heading and keep those headings different and save them within my template. I have a list of the 240 with CategoryID, ParentID, Category and Subcategory all corresponding. These always come and go in CSV format, so I cannot create additional sheets to save within my template without a hassle.
In two ID number value columns, I would like all of the text names replaced with corresponding numbers for upload to the platform that requires them. I have been trying to figure out the best way to create a macro to save within my template for daily use, but so far I have not been able to figure it out. I have been watching videos on vlookup, index & match, find & replace...I have not seen what I need just yet
OK, SO HERE IS MY PRODUCT TEMPLATE SO FAR: [URL]
I'm looking to identify a keyword in a string and then replace that string with just the keyword.
The string is a product description. The keyword is a product group. The keyword can showup in any position in the string.
I can't post the actual data do to confidentiality requirements. but here's an example.
description
qty
price
keywords
code
green grapes bunch
1
2.5
[Code] .......
I have a large keyword list
lets say it's 25,676 keyword phrases all in ColA (Always from A3 downwards).
I delete rows as I'm sorting so the number does very.
The sheet tab is always called "All KWs" (For All Keywords Not surprisingly)
What I would like is a macro that does the following.
Can it look at all the keyword phrases on sheet "All KWs" in ColA (From A3 downwards and make a list on a new sheet of just unique keywords from the list.
This sheet will be called "Unique keywords"
So, can it return on this sheet called "Unique keywords" all the unique keywords from the large list in ColA (From A3 downwards) on the sheet "All KWs".
If it can return only unique words and numbers etc, removing all duplicates.
If it can do this From A3 downwards.....
What I'm trying to achieve is the following;
I have a big keyword list saved in Excel.
Something like the following,
These are all keyword phrases;
car rent
car hire
cars for rent uk
etc etc etc
All listed in Column A
All phrases in Column A, and in separate rows.(1 phrase per row.)about 2000 lines (Rows) in total.
What I want to know is there any way of selecting the whole list and exporting it (To save it as another list. A list of just unique keywords?
So, It would create a list like;
car
rent
uk
hire
for
etc etc etc.
So basically I want excell to look at all the words and export them to another list showing just unique keywords, 1 per line.
This is so I can see from a huge list what all the unique keywords are.
Is there a way of doing this within Excel Now?? or has someone made a plugin ( Macro) to achieve this?
I've looked at the sort & filter options, but it doesn't appear to have this function?
I am trying to create my own budgeting tool. I have most of the tracker set up but I hit a stumbling block. I downloaded transations from Bank of America as a CSV file into excel. I want to categorize these transactions.
I created a keyword list that has words like "Starbucks" "Exxon" etc. Each of these keywords is then listed next to a category like "food" and "gas". So I want a formula that will compare the transactions to my keyword list and return the category type. Here is an example transaction:
CHECKCARD 0256 STARBUCKS USA 00029801 GAINESVILLE VA
I have a keyword "Starbucks" in f2 and "food" in g2.
Eng list.xlsx
The attached file (a copy of my main one) has a list of our engineers, and what stock they carry. The stock parts are the 64, 65, 66... numbers.
I need to create a list from this (as underneath the main table), for all instances where there is a 'Y' in the columns next to each engineer. So if an engineer has 3x pieces of stock, they need to appear in the list 3x times. If they have 1x piece of stock, they appear in the list once.
So basically I have an Excel sheet which has keywords that need to be entered in Google search. I need the URL of the first page of the search result that appears after that keyword is entered. IS there a macro for the same?
View 3 Replies View RelatedI have a drop down list of locations, which when one is selected I would like to show an abbreviated version in a different cell (say B5). I have tried the following so far:
=IF(A5 = "London", "LON")
But I would need to do this for around 30 locations and would need the abbreviated version in the same cell.
Because file size is large therefore I have uploaded the file to megaupload. Click the weblink below:
[url]
Is there formula or UDF which I can use in Column W in Pivot by Week worksheet tab so that I can consolidate all jobs for machine based on shift by day?
Have look in Column W in Pivot by Week worksheet tab for a sample for desired solution.
For Instance, in cell W7 I have used manual formula to consolidate all jobs for G16 Day.
=C9&", "&C10&", "&C18&", "&C19&", "&C20&", "&C21&", "&C23&", "&C24&", "&C25&", "&C32&", "&C36&", "&C37
All job that have a count is greater than 0 is included in my formula.
I need to consolidate the same for other machines as follow: ....
I have found ways to count cells but what I am trying to do is in column F I have a list of meeting topics, and sometimes these repeat in a year. in my drop down menu I have all of them listed however my supervisor wants me to add a count after the meeting number in the 1_1X format where x is the number of times a topic has been used.
The output will be added to my macro here
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim y As String
Dim z As String
Dim b as Integer
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("MEETINGNUMBER")) Is Nothing Then Exit Sub
[Code] .......
how to count the unique occurrences in column A based on the value in column B. In the example below, I am trying to count the number of unique names in each state (Illinois=1, Colorado=2, New York=2). I would like the formula to count unique occurrences for the entire column A because I will be adding to the list.
..A.............B............C
John......Illinois
John.....Illinois
John.....Colorado
Alex.....Colorado
Alex.....New York
Steve...New York
Steve...New York
I realise this is beyond me and wonder if it is possible to list uniqure values in excel and place a count occurrences of each. I have a hugh list of over 400 alpha code i.e ABCD, ABCD, ABCE, BDCE, BDCF ZXYE etc etc. What I'd like to do is pick out all the unique values so I can see what alpha codes are on the list. But then I would also like to count the occurrences of each value/name so I know how many times they appeared on the list.
Col A
ABCD
ABCD
ABCE
BDCE
BDCF
ZXYE
ZXYE
YEZY
CDEF
Col B is where i would like to list all the unquie vaules with a count e.g.
ABCD 2
ABCE 1
BDCE 1
BDCF 1
ZXYE 2
YEZY 1
CDEF 1
Ive been having lots of trouble programing a multi column listbox to play ball. On the form there is 1 tree view and two listbox's. The object is to double click a list 1 item and have that go into the list 2.
Connected to the listbox2_dblClick.. Takes the selected list item and adds to columns 1 intergra value. This places a annoyance to me where listbox 2 is not fully populated (you will see ive fully populated the list during my own testing) and a item is selcted if double click on a blank part of the listbox it will increase the int value of the selected item. I though of populating the list with blanks (used "." as visual) so a cheek could be made on the double click item and if blank do nothing.....
Row Number (A)
ID Number(B)
No.of Repetitions(C)
1
1234
4 or TRUE
2
2538
1 or FALSE
[Code] ............
In need checking if a particular ID number is repeated more than once in column B. I need to write a formula in each cells of "No.of Repetition" column or Column C to check if respective ID number in column B is repeated more than once and display the count or display a condition true or false.
Say I have a certain range in excel from A1:C10. All of these cells contain one of three strings: Math, English, or Reading.
Given an input of ONE of these three (for example, a user can select "Reading"), the program should output ALL the row&column numbers of each occurrence of "Reading" in the range. Ideally, this would appear in a column.
Consider the following image that contains a sample range:
Screen Shot 2014-07-08 at 12.26.15 PM.png
If "English" is the input, the output should be (in another column)
A1
A7
B3
C2
C3
So, I have two columns on a sheet that all contain drop down boxes for various information. We'll refer to them as column 1 and column 2.
In column 3, I want to have the highest instances of those words sorted with the highest on top, and downward from there, along with the number of times it appears in either column 1 or 2. (And I want the sorting to be dynamic, i.e. when I change what's in column 1 or 2, it will re-rank them according to highest number)
i.e.
Column1 Column2 Column3 (Column4-#)
Bob Tom Bob 5
Joe Bob Tom 2
Bob Joe 1
Tom Bob
Bob
I have the drop downs and what not for the 1st 2 columns, I just need to know how to make 3 & 4 happen.
I have one column with names and another with date ranges (JAN06) etc. I
want to count the number of times a name occurs within a given date range.
I want to count the number of times a certain value is found in columnB, only for unique values in columnA.
I have made a simple example file, attached. Column B shows Status which can be Active, Current, Dormant. Column A holds Item names. I want to know how many Active Items I have, but an Item can appear more than once in ColA, so I want it only to include in the count the unique ColA values.
I'd like to count the number of occurrences a value appears in a column. Hard to explain what I'm after so I will draw it out:
Column A
2512
2512
2513
2513
2513
2513
2518
2519
2519
2519
I want to add a formula in column b that will add the number of times it appears as it appears:
ColA ColB
2512 1
2512 2
2513 1
2513 2
2513 3
2513 4
2518 1
2519 1
2519 2
2519 3
I have created (pieced) together a macro to search through a list and paste the results in another worksheet it works fine but i can't get it to loop through the list.
Sub Findall()
Dim y As Long
Dim starta As String
Dim tr As Long, tc As Long
Dim sr As Long, sc As Long
Dim s As Worksheet, t As Worksheet
Dim SourceCell As Range
'Setup
Application. ScreenUpdating = False
Set t = Sheets("Target Sheet")
Set s = Sheets("Search sheet")
Set SourceCell = ActiveCell
Do While IsEmpty(ActiveCell) = False
Counter = 1
'Get last used row in Target Sheet
t.Select..........................
I am working on a big project involving baseball statistics, evaluating individual seasons. After a great deal of entertaining data gathering, I have a spreadsheet of 1896 seasons that meet certain criteria--that is, seasons belong to an individual player. The player's names are in column A of my spreadsheet.
Individual names appear between 1 and 15 times. I would very much like to generate a quick table (or just a couple of columns) that lists every name in column A, and how many times it appears. My understanding is that the histogram function won't work with text. I'm running Excel 2013 on windows.
I get this work a lot and am looking for a much more effective way of working the dataset,
I have numbers like 1.1, 1.2, 1.3 and so on.......
These come under criteria like (1) = 1.1, 1.2, 1.3 and so on.....
I am look for an automated way of doing a count of any number that falls under this criteria, so I want to count based on criteria (1) it would count all 1.1,1.2,1.3 and so on as one count.
I am attaching a sample document to see how it is laid out. [URL] .....
I've got two columns, one with policy numbers, the other with error codes.
A policy could be in the list more than once, so I'm counting the number of unique policy numbers with the function
=SUM(IF(FREQUENCY(A10:F10000,A10:A10000)>0,1))
I want to count the number of errors that are NOT code 00 or code 21. Because the error codes are saved as text, I'm counting the number of errors with
=SUMPRODUCT((B10:B10000"21")*(B10:B10000"00")*(B10:B10000""))
The problem is that since a policy number could be listed twice, when I count the errors, I might be counting the same policy as an error twice. This means that I could feasibly end up with more errors than I have unique policies, which doesn't work.
What I'd like to do is only count the number of errors that have unique policy numbers. That is, I only want to count an error the first time it is in the list, based off it's policy number. Is that possible?
Here's my sample data: [URL]
I have a column of dates (Column A) with intermittent blanks and I need to display the number of occurrences for each unique date.
This is what I am starting with: (Note= The number of rows varies by project so I need the whole Column A referenced)
Dates
02-24-2013
02-24-2013
12-15-2012
[Code].....
Been working on this issue for the last couple of days with no luck. I have a column of data that is a data and time stamp, for any date or time like below. This is an ever growing list as users continue to populate the data.
13/04/2014 20:00
28/04/2014 17:30
09/04/2014 13:30
09/04/2014 14:00
03/05/2014 7:30
03/04/2014 11:10
On a separate sheet I have a list of times by 15 minute increments starting at midnight until 11:59PM like below.
12:00:00 AM
12:15:00 AM
12:30:00 AM
12:45:00 AM
I'm trying to count how often a time stamp occurred between two 15 minute increments. ventually I'll be plotting this data on a graph to show where most occurrences happen.
I would like to design a macro that can find a value within a cell in a colunm and copy a value in the row then count occurances of that value next time a match in the colunm is found on a different row, but also do this only if the date range in the row is within a specified range. So a multi-critieria search and copy operation.
To explain a little better here is a simplified example of my spread sheet that is in input for the macro.
A
B
C
D
E
1
PART NO.
date in
date out
comp1
comp2[code]......
Column A lists the coponents found on the same row as the seach critria, in this example "12-3".
Column B lists the number of occurances of the components. This search is limited to a date range input by the user.
So what I can see is the components replaced and the amount of times replaced on a specific item over a fixed time.
*It is difficult to describe this problem without showing the macro output, but this is just an example for the purpose of explanation.
I know a bit of VBA programming and initially I started with an input box that asks for the part number and limit dates.
I'm trying to write a formula that will count the number of unique occurrences in a column, if a specified value is found in a different column.
So I want to count the number of unique values in the "ID" column if let's say the text "NameA" appears in the "Name" column.
ID Name 12345
NameA
NameB
NameA 12346
I have a table that lists the months of the year down from cell A2:A13, and days of the week along row from cell B1:H1. The data in between (cell B2:H13) is pulled through from elsewhere in the workbook and is in number format.
What I basically want to do is create another worksheet with the months of the years listed down column A, and in column B, for each month, I want the first day of the week where the value in the original table is more than zero, and in column C the second day of the week where the value is more than zero. I really hope that makes sense, was quite difficult to explain!
I am using Excel 2010. I need to copy a list from any Column on the right to the existing Column. See the sample file.
View 10 Replies View Related