I am looking to find a cell value on a spreadsheet that has cells with multiple values listed in it. For example. If I CTR F for i.e. (745) on a spreadsheet that has this value in a cell that contains i.e. (684, 362, 745, ABC) all in one cell I get a no results. Ultimatelly, I'd like to run a Vlookup function on this file.
View 7 RepliesI have three parameters that users on my spreadsheet will be using. They will be searching using the 'Brand', 'MPAN' and 'Tariff' fields.I would like to have drop down menu's for then to select the correct search option. I then have another sheet within the same workbook with all the data in it with columns headed of Brand', 'MPAN' and 'Tariff'. It has to be an excat match using those three parameters though. I would like the parameters the user selects on the first sheet to be searched and found on the second sheet, with the figures in the cells next to the search result getting fed back into a set cells back on the first page.
View 9 Replies View RelatedI have run into serious trouble with a file I am working on. I have 12,000 companies (roughly), and each of them appears in an Excel file multiple times (some 1 time, other 18 times) with a few parameters. The big file (about 90,000 entries) is sorted so it looks like below:
I will illustrate for 2 companies:
Company A 0 200
Company A 0 0
Company B 300 400
Company B 0 50
I need for every company to do a countif for each column on condition
that the value is larger than 0.
In this case I would end up with:
Company A 0 200
Company A 0 0
Countif result 0 1
Company B 300 400
Company B 0 50
Countif result 1 2
However, I need to extend this to the whole 90,000 entries and 12,000 companies and this cannot be done manually. I thought about vlookup but that returns the first entry only, and I have multiple.
What I'm looking for is a formula that will search a data table on a source tab and find a value in a column. And have the ability to function like a vlookup and display the contents of cells in the same row on a different tab. The catch is the value will repeat it's self at random interval in the data table. So for example the values are ON-1 to ON-8 and MB-1 to MB-8. Each value will have it's own tab and I want the formula to search the Source tab and capture the value in order but on the next row of the spread sheet.
View 4 Replies View RelatedI have 2 columns, A (has the names of employees) and B (has the month in which the employee has a project scheduled). What I’m looking to do is find the latest month an employee has a project scheduled. Note: Employees can have a multiple number of projects so they may be listed multiple times with corresponding months.
I have each employee listed in column F and am looking to find their latest project month in column G. (I have the number that the formula should return in column H).
Look in Column A and find the cell that contains "Std A 1" and the cell that contains "Std A 2".
Compare the corresponding values in Column B, and multiply the lowest value by .2
I've tried using various methods (find, if cell.value.instr) but I keep getting errors. Can anyone point me in the right direction?
I have 2 sheets that i need to find what the same valued cells are.
1 sheet has names and i need to find the rows where those same names are on the 2nd sheet.
The formats are different (upper-lower case) and there is additional text as part of the cells in the 2nd sheet. (i.e prod123.web.corp.com) and i just need to find the prod123 parts.
Column A:
pants A green
Pants B red
Jacket C green
Jacket D blue
Polo C white
....
I need a formula to find some value( in our case "colors" = "green, red, blue, white" ... = 20-30 values), and to return in column B, the values, like:
Column A: Column B:
pants A green | green
Pants B red | red
Modern green Jacket | green
blue winter jacket | blue
Polo white D | white
I have different product feeds (csv) and i will like to search for colors in the product names.
I want to find sum of all 4 returned values (Sales!BL5, Sales!BM5, Grants!X5, Deferred Pymts!X5
Column References:
Cell AD2 (AMOUNT TOTAL PYMTS)
Cell AC2 (TOTAL NO. OF ORDERS)
Cell Range B5:B4833 on multiple pages (CUSTOMER NAME)
Cell Range AJ5:AJ4833 on multiple pages (CUSTOMER NAME)
Formula:
In Cell AD2 of CustomerData! Sheet:
If AC2 is not blank, then
look for match between A2 and Sales!B5:B4833, return the value in Sales!BL5, and
look for match between A2 and Sales!AJ5:AJ4833, return the value in Sales!BM5, and
look for match between A2 and Grants!B5:B4833, return the value Grants!X5, and
look for match between A2 and Deferred Pymts!B5:B4833, return the value Deferred Pymts!X5
Hope this does not confuse, but if John Doe is listed in AC2 I want to find the sum of his total payments if his name is listed as a customer in Sheets Sales, Grants, and Deferred Pymts. The Sales! sheet is divided into AM and PM pricing which explains why Sales! has Column B and Column AJ for Customer Names.
I am trying to find 3 values (X,Y,Z) in row A and concatenate those three values in column A for the entire range below, say A2:A100.
View 9 Replies View RelatedI have data similar as shown in the following:
A1A11
B2AA1122
C3B22
D4C33
D44
The idea is to add compare the cells of the first column with the third column. Where same letters/words exist, the corresponding value of the first column should be added to the second column (where no letter exists equally, the space remains empty), so it will look like this
A111
AA1122
B222
C333
D444
the third column always will have at least the same letters as the first column, but new letters/entries can occur.
SM extract 8.21.2013 Cust-sample.xlsx
I want to highlight the cells under System Name (col 1) in sheet 1 that are found in Host (col 1) in sheet 2 "OCPtabvHost"
The text isnt in the same format in the 2nd sheet (it is lower case and has additional text). I need to find the duplicate roots
So I have a list of organizations in Column A, with multiple names (anywhere from 0 to 50 names) for each org. in Column B.
Org
Names
Company A
Brown, Jones, Smith, West
[Code]...
I want to do a find and replace for all of Column B, where all the names are replaced with their respective color values. If possible, I'd like this to all happen with the individual cells (so for example, B3 might go from "Brown, Jones, Smith" to "Red, Red, Blue").
If that's not possible, I could divide all the names into individual cells and then find and replace.
As in attached file, I have 5 tables:
Table 1: matrix that contains Weight data in each cell
Table 2: matrix that contains Code data in each cell
Table 3: Relationship between Code and Level data
Table 4: Table with relationship between Weight data, Code data and Level data
Table 5: I want to populate each cell in Table 5 matrix from getting values from Table 4 by looking up the right value from Table 5 based on :
1) Weight data from corresponding cell in Table 1
2) Code data from corresponding cell in Table 2
3) Code and Level relationship.
for e.g. to get value for Table 5, Worksheet cell B140 ( intersection of A1-A1),
1) corresponding weight is 1.17 as in Table 1 (Weight data) (Worksheet cell B3)
2) corresponding Code is 6 as in Table 2 (Code data) (Worksheet cell B35)
3) Code 6 corresponds to Level 2 (as seen in Table 3)(Worksheet cell A74)
so, looking up the 3 values from Table 4 i.e. Level 2, Code 6, Weight 1.17, one gets cell AR98 with corresponding value. therefore, final value in Table 5, Worksheet cell B140 (intersection of A1-A1) will be equal to $4.00
I have been trying to solve this using Vlookup, Hlookup, Index/Match but am getting confused. Also, not too sure if this can be done in 1 step. New to these excel and these functions
The old thread is here: [URL] ....
There are three sheets in the workbook, Project, Tasks and Details and the expected resulting sheets are RESULT, In_Tasks_but_NOT_in_Projects and In_Details_but_NOT_in_Projects .
But now what I am looking for:
1. Copy the Projects data as is in the RESULT sheet.
2. Then in the Tasks sheet, if the ID matches paste the matching rows under the data from Projects (as in the result sheet with Orange colour)
3. If the ID is present in Tasks but NOT in Projects then copy it into the In_Tasks_but_NOT_in_Projects sheet.
4. Then If the ID and the Name in the Details tab matches with the data in the RESULT sheet then paste it under the ID and Name (as in the result sheet with Green colour)
5. If the ID does not match the ID in the results sheet then copy that row into the In_Details_but_NOT_in_Projects sheet.
The result of the current macro that RHCPgergo worked with are in the last sheet.
The formatting and colour of the rows doesn't matter, it is more of nice to have.
Script Example.png (Picture)
Script Example.xlsx (Example Workbook)
I have a report that I pull that I pull fairly often that is in this format and shows which footage of products each store is getting (out of over 1800 stores)!
Above is the format that it comes back as. And here is a spreadsheet that shows what steps I take to find correct values in detail.
So each FTG has an ID# and Desc. The ID is in text format and each ID is seperated with a comma, no space. Description also. Date is seperate by a space and comma. Stores change footages a lot so I want to find out which footage is effective today (1/27/2014) For ex: Store 63 would have the 5ft effective right now because we are between 5/23/08 and 5/22/14. So each ID# and Ftg Description is in the same order as Date.
When I have hundreds of stores, it is difficult to go through and get each one (even with the way I've been doing it.) But my ultimate goal is to create a macro to put only the current footage ID#, Desc, and Date in Columns C,D,and E. I'm not sure even where to start with doing a macro.
I usually just find the store with most ftgs, count them, insert that many rows after ID and Description columns, then do a text to columns (comma, delimited), sort by 2nd date column (so they all come up top) and then manually go through them and delete unneeded columns once I have them all.
I have an excel workbook with 60 sheets (each contain data in the same categories and in the same column locations, just different information on each sheet). What would the VBA code look like if I wanted to manually enter the find and replace values and perform the function (find and replace) across multiple sheets in the workbook?
View 5 Replies View RelatedI want to build a database where the user can select a specific time period. I have a static file with the time periods and the related values. For instance I have in column A1:A15 the time periods from January 2011 to Dezember 2011, but I have some dates multiple times as there are more values attached to it. For instance I have in the first 4 rows January 2011...what I want is when someone puts in the value January 2011 in cell B1 and Dezember 2011 in cell B2 that the whole static file gets copied to another location (including the multiple dates) displaying the chosen time period. Similarly if someone puts in the value March 2011 to November 2011, I want only those values to be copied.
View 4 Replies View RelatedI maintain the data flow at my work. We send and receive the data using excel files with specific formatting that I then upload to the database. Each time I send or receive the excel file I must log them, this is what my code question refers to.
I use RDBMerge to merge all the contents of the 100 plus excel files into one worksheet. The first part of the macro cleans up the merge data for use in the log (i have attached an example of the clean data and finished log).
The blue shaded area of the "Raw_Data" is what the clean data looks like, the yellow column is what current macro records for each record.
As you can see by the example the Raw_Data is only two files LL_LLL_BOB_ToLLLLL_20121228_01 & LL_LLL_BOB_ToLLLLL_20121230_01, each with more that one record.
The log code in column "H" Is based on this criteria:
First Letter of the Unique ID in column "E" - O, M, or L
Program Type in Column "F" - U or R
1. O-U = U
2. O-R = RU
3. M-U = U2
4. M-R = R2U
5. L-R = R
You will note that Columns G-R of the "Log Sheet" correspond to the "Record Type" found in Column "G" of the "Raw_Data" sheet.
This is the area where my skill at using scripting dictionaries fails.
The results for the log list each file only once, but the log code for each corresponding "Record Type" in columns G-R of the "Log Sheet" must contain each unique instance of the code. In other words
if LL_LLL_BOB_ToLLLLL_20121228_01 contains an O-U with an "A" Record Type and an M-R with an "A" Record Type; then, on the log sheet there needs to be the codes "U/R2U" in the cell intersection of the LL_LLL_BOB_ToLLLLL_20121228_01 record row and "A" column (which is column "G")
So, If the File contains one of each code for each Record Type the corresponding cell must house one of each code separated by a "/" without any spaces. This means the cell value could no code, or one code and all the variations in between to all five codes. Also, for ease of human reading the log codes should be concatenated in the 1-5 order that I listed them in (U/RU/U2/R2U/R)
Here is my code so far.
VB:
Option Explicit
Sub test()
Dim dic As Object, a, i As Long, rng As Range, e, w, n As Long
Set dic = CreateObject("Scripting.Dictionary")
[Code].....
two formulas for one data set. The data is attached in the spreadsheet: "Product IDs". The data is a set of Master Product IDs (parent) and the Linked to them Products (children). I need to create a relationship between unique parents (Master Product IDs) and their children (Linked Products)
I need to create two formulas:
1. From the Data Set table, need to vlookup the unique value in column A (Master Product ID) and return comma delimited (concatenated) corresponding values from column B (Linked Products). So, the result will be as shown in Table 2.
2. From the Data Set table, need to vlookup the unique (de-duplicated) parent/children relationship in column A (Master Product ID) and return comma delimited (concatenated) corresponding values from column B (Linked Products). There are total 3 parent/children relationships in Table 1. So, the result will be as shown in Table 3.
I’ve got a folder on drive C named “Customer Sheets”. In this folder at any time there could be over 500 individual customer sheets. i also have a "Summary Workbook" on drive C
What I need is a macro in my "Summary Workbook" that copies cells (“B3, B6, B12, B13, G5, G6”) form every customer sheet in my “Customers Sheets” folder and paste it into my “Summary Workbook” onto sheet2, every customer in a new row.
The “Summary Workbook” should run this macro every time it is opened or on my command so that the database is continually refreshed when a new customer sheet is made.
Sheet 2 is the problem.
I need to match cells e3:h3 whenver the dates change in in cells c5:c9 and then put there result of cells b5:b9 into the corresponding cells of e5:h9.
Basically what I'm saying is that I want to keep a track of all previous pay amounts from each pay period. So when the next period changes the date it also copies the new pay amount to the corresponding date of the previous pay section.
I have included a sample.
I am trying to search for multiple values in a cell with Multiple criteria.
E.g: Please find attached the sample excel data.
I tried using VLOOKUP which is not working as I am having multiple values in column A.
if there is a way to split multiple values in a cell to multiple rows. example check the attached sheet, macro/function with in excel etc.
View 2 Replies View RelatedI have attached an example worksheet which I think will explain better than I can but here goes.
There is a user form which forces the user to select from all the three comboboxes boxes.
The comboboxes are NOT populated from the worksheet.
The Date of Shift get today’s system date and the previous days date.
The shift pattern and shift duration are set entries.
When the update button is hit the object is to look in column "A&B" to see if a row has an existing entry which matches the user’s entry.
If an existing entry is found then it will update the cell in column "C" in that row for the matching DATE and PATTERN
If no matching entry exists it will append a new row with the user entry.
I have been trying to do this for a while trying what I know and then I found the Find matching X sample written by ozgrid.
Tried it out and found that it used the columns to populate the comboboxes and did not like using a date also I do not need a list box
So I decided to try and modify ( 2 whole days over the weekend, so I am no programmer just try as best I can) it to suit the needs outlined above.
I have reached the limits of my skill and now get "Object Variable or with block variable not set"
I have big library books database file. now i want to re check all the book with physical stock and mark in excel file.
i have one more file with books barcode no.
now i want to mark multiple cell in sheet..
multiple search match and replace content in a different column so for example
new workbook (look up table)
sku
search1
search2
[Code]....
so something like where you compare two tables and find and replace based on another cell that matches in my sku.. more details would be if the table column aren't exactly matching but the column header and the row header would match and fill or replace in the correct/corresponding cell is there a macro or vba to do this job in excel?
I want to identify the 2 minimum valued greater than 0.00% in rows with percentages only. Then identify the 2 maximum values greater than 0.00% in rows with percentages only. The sheet changes in size (rows and columns) each day. C4 Row 4 is a constant starting place if that is necessary. I have been tweaking this code all night, but no luck. I keep getting a highlighted yellow error in editor @ oRg.Find(What:=iMin, _
I also don't know what an oRg is, I am using 2001, that may be the problem.
I have a workbook with 2 sheets - [Names] has with a list of Staff Ids column B, [Access] has a list of their system acces - with their staff ids in column A and access in F (There could be more than 1 type of access),
I am looking to create a code that :
For each ID in [Names] B lookup in [Access] A
if found create a string with value in F ( so I have a list of all access )
And paste into F in [Names]
I have tried all sorts of things and just can't get it working.
i'd like formula to find value above and below specific cell
for ex
i have value in cell c5
i want formula helps me to find
values above c5 in cell c4,c3,c2,c1
and
values below c5 in cell c6,c7,c8,c9