Build List From Common Cells
May 17, 2007
Got something I need to do that I have never done.
I have a column on a worksheet1:
NBC//1
ABC//1
ABC//2
ABC//3
ABC//4
CBS//1
CBS//2
I have a column on another worksheet2:
NBC
ABC
CBS
In the column next to the above on worksheet2, i want to pull the numbers in for that (the numbers are contained in ONE column):
NBC | 1
ABC | 1 2 3 4
CBS | 1 2
Im not sure how to go about this?
In the example I attached, on sheet 2 column B, that is where I want the numbers to build to.
View 9 Replies
ADVERTISEMENT
May 14, 2008
This below code i find out from this form Skip the row find next
but i need it i have column fom c to M
if the cell k valur or j value = 0 or empty skip the enitre row go next row ifthe kvalue <> 0 or <> empty
do something
the below code where do i moify .
Set c = .Find(MyInput, LookIn:=xlValues, SearchOrder:=xlRows)
If Not c Is Nothing Then
Count = Count + 1: ff = c.Address
k = c.Row
Redim Preserve wsName(1 To Count): wsName(Count) = ws.Name
Redim Preserve rng(1 To Count): rng(Count) = c.Address(0, 0)
Do
View 3 Replies
View Related
May 29, 2005
I have a spreadsheet of part #'s, descriptions, manufacturer names, and manufacturer part #'s. (It's a list of the inventory in my warehouse). Each row contains information for just the item in that row. Row 2 references another part in my warehouse, row 3 yet another, and so on.
Many of the parts have more than one potential manufacturer and part #, (meaning that any of those manufacturer's part #'s are basically the same tool; just different brands. At one time we may get a shipment of one, at other times we may get a shipment of another). For example, a screwdriver may be listed like this:
Part # 1234 screwdriver, mfg Snap-On, part # 456, mfg Stanley, part # 789, mfg Mac Tool, part # 439.
Then further down the list, there may be another part listed like this:
Part # 9980 wrench, mfg Stanley, part #741, mfg Snap-On, part # 852, mfg Proto, part # 369.
If you can imagine that data across the cells of a spreadsheet row, notice how the mfg name 'Snap-On' was the first mfg name on the screwdriver, but it was listed as the 2nd mfg name on the wrench.
So, here's my question: I want to be able to group all of the items made by any one manufacturer together in a new list. If all of the manufacturer names were in the same column, I could simply sort the list by that column, but since I've got thousands of rows with the mfg name I'm looking for in different columns on different rows, I thought maybe a macro could search each row for the word I'm looking for, then if found, take the whole row and copy it to a new worksheet. So the end result would be, If I wanted to see all items of which Snap-On is an acceptable supplier, I could get a list of all potential Snap-On items grouped together.
I'm sorry this is so long. I may have over-worded this and it may not be too clear. I could email an example of the spreadsheet if anyone needed more info to figure out what I'm looking for and was willing to take a look at it.
View 9 Replies
View Related
Nov 13, 2008
I am trying to build a list to put into Microsoft access
Cell A1 has "
Cell B1 has 8W###
Cell C1 has " Or
I want a formula that makes D1 "8W### Or ". edit: I got it =(A1) & (B1) & (C1)
I kept trying to use "" and it would work.
View 2 Replies
View Related
Aug 27, 2009
I have a workbook with two spreadsheets, one blank and one with a Header row and a list of employee names and random dates that will continue to be added to at the bottom per a copy and paste.
What I need is to have a form dropdown on the blank sheet (per a cell reference) that shows employee names and dates that has that name next to it in the data sheet.
The employee names are in three columns Last, First, and Middle and the dates are in a separate column (A).
I guess I need a VLOOKUP that can take the range and pull into the dropdown the employee name and every date associated with that name.
View 10 Replies
View Related
Apr 14, 2013
I am currently trying to organise a student yearbook for my pupils at school. We have given out sheets to students in order for them to nominate their peers for various awards such as 'Funniest Student', 'Most Caring Student' etc.
I am trying to collate all of the data into an excel workbook. I have made two sheets within a workbook, which I have formatted how I want them to look like. I want to insert all the student's responses into one sheet, and somehow, the top 3 most occurring nominations are found and are inserted into the appropriate cells in the separate sheet.
I have attached a copy of the workbook. Due to data protection the student names and tutor groups have been taken out and replaced with the word "REMOVED", yet I will paste them back in at a later time. So feel free to have a play around to see what I am talking about! I have filled the table with some dummy data just so you can see what I mean.
I am not at all sure how I would go about picking out the top 3 most occurring names for each award, but once I can do that, I will be able to insert them into the other sheet. How to automatically produce the top 3 most occurring names.
View 1 Replies
View Related
Sep 25, 2007
I have 2 columns of data looks like this:
col-a
shark
whale
dolphin
seal
fish
col-b
dog
cat
fish
deer
moose
What I'm looking for is a formula to compare col-a to col-b and create col-c to look like
col-c
dog
cat
deer
moose
View 9 Replies
View Related
Jul 25, 2012
I've found a vb script via Google that displays the most common words in a list followed by a number.
Code:
Sub HTH() Dim vArray As Variant Dim lLoop As Long
Dim rCell As Range With CreateObject("Scripting.Dictionary")
For Each rCell In Range("A1", Cells(Rows.Count, "A").End(xlUp))
[Code] .......
At the moment this results in 1 word followed by a count.
How do I alter this so it shows 2 or 3 words, ultimately showing the most common phrases?
View 1 Replies
View Related
Jun 25, 2008
I am attempting to take a very large list of keywords, and find the most common words and phrases within them. For example, if I had a list that said:
excel formulas
excel spreadsheet formulas
excel help
excel formulas help form
formulas for excel
I would like to come away knowing that "excel" and "formulas" are common words within the list.
Currently, I believe this can be accomplished by doing the following:
1. Break down each line into all of its possible combinations.This would mean that the line with "excel spreadsheet formulas" would return:
excel spreadsheet formulas
excel spreadsheet
spreadsheet formulas
excel
spreadsheet
formulas
2. Once the entire list is broken down into its many parts, use the pivot table feature of excel to determine how common each of the parts is within the entire data set.
So, my questions are these:
1. Do you believe this is the best way to solve my problem? If not, what would be the preferred method?
2. If this is the best method, what function or script would I use to accomplish the first step of breaking down the lines into their individual parts?
Mike Auto Merged Post Until 24 Hrs Passes;It appears I put too many characters in the title of my post. It should read: Common Words - Decomposing Text Phrases
View 3 Replies
View Related
Aug 1, 2006
I'm trying to write a macro that will build a tree from 3 sets of lists and I'm really stuck!.
The thing is:
1. I have 1 worksheet with 2 sheets. First sheet "Lists" contains in every column a list of data.
2. Range("B:C") contains list od SERVICES1 - every value in list has its corresponding ID_SERVICES.
3. Range("D:F") contains list od SERVICES2 - every value in list has its corresponding ID_SERVICES2 and a parent id ID_SERVICES.
4.Range("H:I") contains list od SERVICES3 - every value in list has its corresponding ID_SERVICES3 and a parent id ID_SERVICES2.
5. Second sheet "Project" in Range("A:C") contain example of tree like: ...
View 3 Replies
View Related
Sep 17, 2013
I am working on a customer report template that generates our customers reports and will send them out automatically.
This issue I have now is that the system that generates the raw data for these reports only lists the Customers name in a column with an entry for each line of data, the thing is though that the system has lots of variations of the customers name, even more so if that customers has different departments.
What I need to do is from this list of customer names, I need to automatically figure out what the "common" name is or main name so to speak, and then make a variable using the correct full name, which will be used later on in the code to import correct logos, and direct the reports to the correct people.
Here is a quick example of what data we get raw from the system:
Customer Name:
John Build
Johns Buildings
Johns Ltd Building
Johns Plumbing Department
Glass Doors Ltd A Department of Johns Buildings
Johns Building Corporation
Hole In One Golf Range
This is just an example, we have thousands of clients, so the length, number of words etc can change alot. Ideally I from a list similar to that I would get a full proper result of "Johns Buildings Ltd" for example, this would then be in a variable to be used in code from then on to reference doing certain things with the reports of Johns Buildings Ltd.
You'll notice there is one name "Hole In One Golf Range" that seems to have no relation at all, this is correct, ideally I would also like to build in some error checking into the code, so that rows like that that have nothing to do with the others would get deleted.
So how would you amazing VBA gurus go about working with data like this? I'd prefer a more general answer with explainations that just straight code, as I'm sure I will have to adapt the hell out of it for it to be useful in context.
View 9 Replies
View Related
Sep 11, 2009
I would like to generate a dynamic list of the top five most common text responses in a range. For simplicity sake, let's assume that the range spans cells A1 to A20, and the list is generated in cells B1 to B5. So, essentially, I am seeking five separate formulas. One to calculate the most common text element, one to calculate the second most common text element, one to calculate the third most common text element and so forth.
I am currently working with the two formulas below but finding it incredibly difficult to merge them into a working format.
Formula for finding second most common number:
=MODE(IF(IF(A1:A20"",A1:A20)MODE(IF(A1:A20"",A1:A20)
),IF(A1:A20"",A1:A20),""))
Formula for finding most common text element:
=INDEX(A1:A20,MODE(IF(ISTEXT(A1:A20
),MATCH(A1:A20,A1:A20,0))))
View 9 Replies
View Related
Oct 16, 2008
I have a worksheet with three columns. First Name, Last Name and email address. I need to combine the first name with the last name and add our @ourcompany.com to the end of it for the emaill address.
I've figured out how to do that, but my problem is, we don't use our first and last names in the email address. We use first initial and last name.
I know how to make Tom Jones become tomjones@ourcompany.com
What I need to know is how to make Tom Jones become tjones@ourcompany.com.
Can someone point me in the right direction here? I just need the first initial of the first name and combine that with the last name and then add in the @ourcompany.com.
View 3 Replies
View Related
Jul 8, 2014
The report that I am trying to build relies on DCOUNTA formulas.
This report has 200 lines and 200 columns approximately. Each of these cells would be a DCOUNTA formula. I have a separate sheet, 'Criteria', where I intend to describe all of my criteria for the DCOUNTA formulas.
My problem is that for each DCOUNTA cell in my report, I have to create 2 rows and 5 columns worth of criteria information... so, for 40,000 cells to populate, I need to create a huge sheet of criteria.
So, while I could do the above, and it would take me some time, more efficient and elegant way to spend my time in order to get this report display DCOUNTA formulas?
For more context, I am trying to present information for 100 individual salespeople, monthly and year to date. For each cell, I need to tell DCOUNTA that the date needs to be comprised in the current period (2 columns), what the name of the salespeople is, and what the nature of the information is, which could be more than 2 additional columns.
I can to some degree autofill most of this, with some OFFSET magic, but its still feels like an ugly way to achieve my objective.
View 9 Replies
View Related
Jan 23, 2006
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
View 14 Replies
View Related
Oct 12, 2011
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].....
View 2 Replies
View Related
Dec 4, 2013
I have scoured the net for the following and have not been able to find a solution. I have found variations but not something specific for the use needed here.
I have attached a file with sample data which has the following format.
Column 1 = vehicle model (in this example golf, jetta, but there will be over 100 choices)
Column 2 = vehicle package option for specified vehicle model (automatic transmission, manual transmission, automatic transmission with air conditioning, etc...)
Column 3 = vehicle colour available for vehicle package option
Please note that Column 2 values for 'golf' are different than values available for 'jetta' (in the sample data I have blocked out common values with the same colour for quick and easy identification)
Also please note that Column 3 values of colour options vary for each 'vehicle package option'.
The behaviour that would be ideal is to have 3 drop down menus. The first drop down menu will allow selection from column 1 and will show each model only one time and repeated values will not be shown. The second drop down menu will only show the options available based on the selection in the first drop down menu. Likewise, the third drop down menu will show the colour options available based on the selection in the second drop down menu.
Since my data will involve hundreds of unique values in column 1 with plenty more added over time, it seems that the format of the data and the way it is laid out in the attached sample is the easiest way to organize it. It is perhaps also the easiest way to include new data without a lot of reprogramming.
Sample Data - Dependent Drop Down Cells Question.xlsx
View 8 Replies
View Related
Feb 19, 2010
I have a spreadsheet that lists two columns - impressions and clicks - by date. Sometimes the same date is listed in multiple rows. What I would like to do is add the columns so that I have a total number of impressions and a total number of clicks by each date.
For example my data could look like:
Date || Impressions || Clicks
01/01/09 || 5 || 6
01/01/09 || 3 || 4
01/01/09 || 4 || 7
and I would like it to look like this:
Date || Impressions || Clicks
01/01/09 || 12 || 17
A shortened spreadsheet is attached. The actual one is for the past year with over 1000 rows, which is why I'm hoping someone can point me in an easy direction to do the summing.
View 3 Replies
View Related
Jun 5, 2007
The compliment of a set is those elements not in the set. Excel doesn't do compliments well.
In Automatic Filter, one can easily see the rows that match criteria, but selecting the compliment of those rows (the ones that don't match the criteria) is difficult.
Similarly, there are the range functions Intersect and Union, but no Symetric Difference.
(Def: the symetric difference of two sets, AB, is the set of those elements in A that are not in B. {1,2,3,4,5}{2,3,4,10,11} = {1,5}.)
Other than by looping through cells:
Given two ranges, aRange and bRange, how would one code for those cells that are in aRange, but not in bRange.
View 9 Replies
View Related
Dec 16, 2007
I have 2 lists in column A and column B. For each cell in column B, I want to put a "YES" in the corresponding cell in column C if the contents (of the cell in column B) are somewhere in the entire list under column A (I think the list in both columns is about 5000+).
I have logged in after quite a gap and found that all my subscribed threads (gathered over a year) have vanished
View 3 Replies
View Related
Apr 23, 2013
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.
View 3 Replies
View Related
Dec 29, 2008
I'm trying to write UDF which getting RegEx pattern and a certain cell as arguments and returns only matching string. For examples for string "The quick brown fox jumps over the lazy dog", and RegEx pattern "w{4}" the function will return two words "OVER" and "LAZY". What should I change in my code?
Function GetPattern(myPattern As String, myString As String)
Dim regEx As RegExp
Dim Matches As Object
Set regEx = CreateObject("VBScript.RegExp")
With regEx
.Pattern = myPattern
.IgnoreCase = True
End With
GetPattern = regEx.Replace(myString, "$1")
End Function
View 2 Replies
View Related
Apr 30, 2013
I would like to sync cells together that contain common words for sorting purposes is this possible? For instance i have a [URL] ..... in column A row 1 and In column B row 2 i have the word bellmont i need to get the rows to sync so rows containing common words line up. I have 8,000 rows to sync?
View 1 Replies
View Related
Aug 16, 2008
I'm glad founding this great forum. I have learned a lot in the last few months.
Now I need to do a task and can't find online a tutorial for this. Here is what I need:
File A contains 23 columns with info for 300 products (rows). File B contains same columns for 280 out of the 300 products from file A, plus 40 new products.
I need to flag in file A the 20 products that are not in the file B, plus flag in file B the 40 products that are not in the file A.
The structure, columns, etc are the same for both files.
View 6 Replies
View Related
Feb 11, 2013
compare some list of values in order to fiind the common values.
View 1 Replies
View Related
Apr 2, 2014
Basically, i have a common workbook template that is used by multiple users across the business to request a cost for numerous new products.
Within the template, there is a common section at the top, where specific project information is entered. There is also a table beneath where 1 or many products can be entered, with specific information relating to that product in the same row.
All the submitted requests are uploaded via an email attachment, to a particular sharepoint directory.
What i would like to do in the master workbook is the following:-
1. Open in turn every uploaded workbook within the sharepoint directory and copy the following cells into the master workbook, each in it's own row (or next available), with the data in adjacent cells.... 1st cell to enter data is $B6.
Cells to copy from each sheet:
Common info contained within cells:
$DG$2,$N$11,$N$12,$N$19,$N$13,$AO$7,$AO$8,$AO$9,$AO$10,$AO$11,$AO$12,$AO$12,$AO$13,$AO$14,$BO$8,$BO$11,$BO$14
Product specific info: $U37, $AD37, $AH37, $DH37, $C37, $O37
Depending on the number of products requested, we need to repeat (loop?) until it finds the next blank row in the table. I have hidden a blank row in the table, so there will always be one!
All of the common information needs to be included for each product specific entry.
For each file, once the upload has been completed, i would like the file to be moved to another "archive" directory.
I have attached the template for information. The master workbook is still in development so can't share currently.
View 12 Replies
View Related
Jan 8, 2009
I have a brand Master in below format in sheet 1
brand codebrand Description10a11b12c13d14e
and Product master in sheet 2
product codeProduct Description000roof paint001floor paint002exterior paint003marine paint004wood finishes
I need in sheet 3 for each brand
Brcode + Product CodeBrand Description and Product description10000a roof paint10001a floor paint10002a exterior paint10003a marine paint10004a wood finishes
View 9 Replies
View Related
May 17, 2014
how to build a formula that would allow my remaining UOM and it's associated cells to move up to the days remaining UOM once my time goes below, say 31 days.
View 3 Replies
View Related
Dec 4, 2009
I need to create a calculator that tells me how long a invoice will take to be paid using my current processes at work, working days only.
I need to imput the date the invoice is received and then for the rest to be worked out automaticly
I need it to do the follwoing ....
View 11 Replies
View Related
Jun 23, 2014
I have been trying to create a macro that would create a schedule for me based on date and a product type. In columns K-N I have an IF statement that tells when a shipment would need to arrive in a row that corresponds with a date (column A is 1/1/2015 - 12/31/2015). On the next page I would like to generate a schedule that lists the dates that all of the products are needed in chronological order. I've tried to use custom functions like finding the nth_occurence but it just gets way too messy with so many shipments.
View 6 Replies
View Related
