Extract All Matching Rows With Active From A Database

Jul 17, 2009

Heres what I need:

On the 'values' worksheet (first one), a formula in cell M2 that will search the worksheet 'oc_users' (second one) for any row containing "Active" in column G, these rows will return the corresponding email address in column A of the same worksheet. Skipping "Inactive" rows.

End result: anyone with an active status in oc_users will have their email populated in the 'values' worksheet.

View 9 Replies


ADVERTISEMENT

Extract Matching Rows From 2 Files

Jun 2, 2007

I had a VBA excel question. I have 2 excel files. Each file has a column on product SKUs. What I need is a VBA function that compares the 2 key columns and extracts the matching SKU and saves them in a 3rd excel file. That is the 3 file should contain only the SKUs that are in both of the 2 files.

View 9 Replies View Related

Matching Worksheet With Database?

Oct 31, 2013

I have this worksheet (see attached file). what i want to ask is how to fill the HS CODE on Sheet 1 based on database ? can i use vlookup?? or there is another formula ?

MAPPING PIB NEW 2013 4.xls

View 7 Replies View Related

Matching Entries From One Spreadsheet To Database

Mar 18, 2014

I run a bowling leagues which as 7 divisions and 10 teams per division, some of the clubs have up to 4 teams entered. I keep a spreadsheet for each division which keeps records of each teams performances as well as individual. All the clubs have to register their players for which I keep a data base for each club, the clubs having 4 teams register quite a number of players. My problem is I have to manually check the data base against players being entered on score cards and then on to the spreadsheet. Down columns B & C on the spreadsheet I have the Forename & Surname, when I enter a name in the cells I would like a formula to check against the clubs data base and return the name or false

Players Reg.xls
2014 stats - Copy.xls

View 3 Replies View Related

Matching Patient Personal Data To That Already Stored In A Database

Jun 16, 2009

I am trying to write a macro to transfer patient personal data onto a database.

On sheet 1 the user inputs the patient surname, forename, date of birth, date of test and the test results.

On sheet 2 is the database, where patient surname, forename and date of birth is stored in columns A, B and C respectively. The remainder of the columns on each row is available for scoring test results.

What I would like the macro to do is as follows:
•Read the patient surname as input on sheet 1
•Check on sheet 2 if that surname already exists
•If it does, check if the forename and date of birth also match to sheet 1 for that particular entry.
•If a match is found then copy the test results into a column on that particular row. E.g. could be copied into column D.
•If no match is found, then all the data is entered as a new row on the database (I’ve written the code for this bit but thought I should add it in for clarity)

This will then allow all test results for a particular patient to be read off the same row in the database.

View 13 Replies View Related

Lookup Value From Database And Get List Of Matching Values To Another Sheet

Sep 21, 2013

I want to find the corresponding Disc Codes from a list and copy them in the DiscName column in the summary sheet. some lab names will have more than one Disc codes so when I run the macro it should bring up all the relevant Disc Codes matching with the Lab name to DiscName column.

Col 1 col 2 col 3 Lab name Disc Name(say abcd) xxxx yyyy zzzz ppppand

The list looks something like this.

Col 1 Col 2 Lab name Disc nameabcd xxxxxabcd yyyyyabcd zzzzzabcd pppppbcda
qqqqqbcda rrrrrbcda iiiiibcda jjjjjbcda kkkkk

View 1 Replies View Related

Formula To Extract Data From Database

Nov 16, 2008

I want extract data from 1 database and I know how to do it with the Pivot Table. Unfortunately, doens.t show it the way I want. I have attached one file with my problem - pls, check sheet RPT.

View 3 Replies View Related

Extract Data From EPR Database Into Excel

Dec 9, 2011

I'm tasked with looking up part numbers in Epicor's ERP database and entering the labor cost into excel. Is there any way to write a macro or VB program that lokks at the part number in excel, goes into epicor, pulls finds the part number and cut and pastes the cost data.

I have about 30,000 of these to look up.

View 1 Replies View Related

Extract Data From Excel Database Based On Criteria

Apr 16, 2012

What I am trying to do is extract all the data from a excel database based on criteria. When i specify the site and month I need to pull all the corresponding data and write it to a worksheet. Below is an example. I am not allowed to show all the data it is confidential.

Site Date Operator Month
Chicago 12/3/11 Daniel December
San Jose 4/8/12 Mike April
New York 4/8/12 John April

View 1 Replies View Related

Compiling Partially Matching Data Into Matching Rows With Macro

Jan 20, 2013

My task is to combine two large databases into one spreadsheet by extending the number of columns. The data from each database is 90% matching based on an identification number, however occasionally there are additional rows or unmatched identification numbers that need to be kept for analysis.

When this happens, there needs to be a blank row inserted to represent the missing data in the rest of the corresponding row.

I am having trouble finding a quick way to do this because I have approximately 12,000 rows (and columns up to DV when combined).

for example:

p1
data
data
data
data
p2
data
data
data
data

[Code] .....

needs to become:

p1
data
data
data
data

[Code] .....

I am guessing I will need a macro of sorts, So far I have made one column that tell me if the ID's are matching or not (1 or 0) and if they are not matching (0) I manually insert the rest of the row that is missing or make space for the duplicate data (which needs to remain).

View 5 Replies View Related

Excel 2003 :: Extract Information From Spreadsheet Database Into Another Sheet With One Trigger?

Nov 27, 2012

I used to use Excel many many years ago for all my basic business databases and pivot table reports (so was at a basic competency level) and want to use it again but it has been quite a while and I can't remember how to do the following.....

I want to build a database of sales and purchase info in say 'sheet 1' - let's say 30 different headed columns and then many lines of different information under each column.

In 'sheet 2' I want to create an invoice template of my own design (I can do easily) for printing.

This template will need to extract say 10 bits of information from a particular row and then put them in a certain place on this sheet.

How can I type in one piece of information in a cell on sheet 2 (say the invoice or line number of the database) and that will then trigger all the other needed information from that particular line in sheet one onto this template?

I know it can be done but for life of me can not remember the formulas to use for these other cells in sheet 2 based on the info put in that one 'trigger' cell

View 4 Replies View Related

Extract Matching Data

Jan 8, 2007

I have a flat file (see attched). What I want is to be able to choose a "Unit no" from a form combo box and a list of names linked to that unit to appear below

View 4 Replies View Related

How To Extract Matching Data From Two Different Files

Aug 4, 2012

I have on excel file like this :

SKU PRICE QUANTITY

1........25...........4
2.........41..........9
etc......etc........etc

The second one looks like this :

SKU UPC
0...786876
1...44777
etc...etc

While the first file contains about 300 active products the second one has a list of about 2000 active and deactivated products. What I need is to extract the UPC from the second file matching the active SKU from the first file so I have a final file that looks like this:

SKU UPC PRICE QUANTITY
1..44777...25.........4

and so on.

View 1 Replies View Related

Extract 1st Matching Substring From Cell

Nov 26, 2008

I am trying to make a simple formula to populate a cell with the first matching substring found in a data cell (from a fixed list of substring keys).

Example of desired results:

Substring data: {X, YY, ZZ} (typically 6 to 15 values in real world use)

Data..........Calculated result..........Comment
AAX.................X........................Substring X was found in Data.

ABC.............[empty]...................None of substrings were (X, YY, ZZ were found)

XYYZZ.............X......................(only first match need be returned)

ABCZZ.............ZZ....................Substring ZZ found etc….

I am looking for something cleaner than nested if statements since there are likely to be a lot of values and different variations. The work would then be to copy the formula and populate the list of substrings to search for in the data. I tried some array formulas using search() but no luck so far.

View 4 Replies View Related

Extract Cell Value From Sheet After Matching Certain Criteria?

Aug 9, 2012

I have two excel sheets. Lets say one is for May and another for June month. I have some data like name of account, account number their ratings . I wish to find if the account present in June month was present in May month sheet or not and if yes, then i need to extract a rating entry( column) from it and paste it in another sheet where i have copied all the data of June month so that i am able to see if the rating has changed over the months or remain same for that particular account...

i am trying vlookup but it is not able to search the name of the account even when it is present in the sheet( like WBIDFC) ... it seems the problem may be because of the format but i am not sure.... moreover i tried to vlookup via account no. but problem is that sometimes the account number is given and sometimes not.. Moreover , the format also does not match sometimes..

View 9 Replies View Related

Consolidate Matching Rows And Delete Duplicate Rows?

Feb 20, 2008

I am working with timesheet data (name, project code, task code, date, hours etc...) in one spreadsheet and rate card data (name, role, day rate etc...) in another.

My task is to pull together some of the information in each of these two source spreadsheets and compile a report. This I have done no problem. However, where a person works on a particular project and task on the same day and records multiple entries (which could be negative) I need to consolidate the hours in all these matching rows and have just one row reflect the total hours worked and delete the other duplicate rows.

So an example would be:

Project | Task | Name | Role | Date | Hours

123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | -2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 3.5

[Code]....

My problem is I don't think I have approached this the right way but am unsure of where to go with it. The code as is does sort of work but I still get some duplicate and zero lines in my results.

View 4 Replies View Related

Copy Rows To Workbook Into Rows Matching Date

Dec 13, 2006

I have 2 workbooks.

The first one (Top Ten Auto Generator.xls) ( Sheet is Summary) has 1 row (13) with 4 cells that have data. A13 with date (today formula), B13, D13, and E13 are numbers. The cell range will be the same each time the data is copied (the workbook has a marco to generate the numbers each day already).

The date doesn't need to be copied, just the other 3 cells data (in the same order) using paste special.

The workbook that needs the cell data is a report ( Dashboard.xls) that has lots of different departments each using 3 columns for their specific data. The left most column A is the date listing to match.

The column Range for my department on that "Raw Data" sheet is "H", "I", & "J" where the "H" would get the data in "B13", "I" would get "D13", "J" would get "E13" for the date that matches the other workbook.

So the way it would work, is that once the vba is run the 3 cells from the auto generator are copied, then the vba opens the dashboard.xls and looks for the date in column A which matches the other workbooks A13 date value, and then the cells are pasted into that row, but in column H, I, & J.

Here's sort of some code that I put together to see if that would be easier to understand. I'm new at doing the vba so I don't have better code.

Private Sub CommandButtonpaste2dash_Click()

'get our data from generator

Windows("Top Ten Auto Generator.xls").Activate
Sheets("Summary").Select
Range("B13,D13,E13").Select
Range("E13").Activate
Selection.Copy
Workbooks.Open "Dashboard.xls"
Sheets("Raw Data").Activate

'look for the date in column A which matches A13 in the Auto generator
Some Code For finding the right date

'once found, paste special to the same row, but in column H (the 3 cells should paste together ok)
some code For pasting into H In the same row As the found date

Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
ActiveWorkbook.Save
MsgBox "Done!!!"
End Sub

View 9 Replies View Related

Delete Rows Matching Criteria & Move Rows

Aug 28, 2007

I have a worksheet in which I have sorted the data based in date and numbering (column I and E). I would like to create 2 macros for following actions:

1- all rows with the value "TOM" in column C will have to be deleted.

2- all rows with a value of 601 or 602 in column E, will have to be moved to the bottom of the sheet after the last row with data. The rows that have been moved will have to be sorted based in date (column I) and numbering (column E).

View 2 Replies View Related

Consolidate Matching Rows And Delete Duplicate Rows

Feb 20, 2008

I am working with timesheet data (name, project code, task code, date, hours etc...) in one spreadsheet and rate card data (name, role, day rate etc...) in another. My task is to pull together some of the information in each of these two source spreadsheets and compile a report. This I have done no problem. However, where a person works on a particular project and task on the same day and records multiple entries (which could be negative) I need to consolidate the hours in all these matching rows and have just one row reflect the total hours worked and delete the other duplicate rows. So an example would be:

Project | Task | Name | Role | Date | Hours

123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | -2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 3.5
123456 | 1.001 | Jo Brown | Developer | 20/02/2008 | 7.5
123456 | 1.001 | Jo Brown | Developer | 20/02/2008 | -7.5
123456 | 1.001 | Sam Smith | Architect | 20/02/2008 | 7.5

Should be processed and come out like this:.......................

View 2 Replies View Related

Delete Blank Rows From Excel Database

Apr 30, 2012

Any code for deleting empty rows from a database, without sorting the database.

I am trying to do a for..next loop going through each row but can't work out the code to check each row as to whether it is blank.

View 4 Replies View Related

Comparing 2 Columns Have Similar Data - Extract Matching Data

Apr 18, 2013

So I have this problem in excel with comparing 2 columns.

Basically, I have 2 columns(a &B) that I need to compare with one another and find out the matching data. I am trying to use that matching data to enter in our reports.

Both columns might have duplicate items because I am dealing with premium numbers.

Formula to compare these 2 columns and find the matching data and extract it to a separate column. Keep in mind, i am dealing with almost 20,000 lines of data.

View 2 Replies View Related

Matching Rows On One Sheet With Rows On Another

Apr 27, 2007

I'd like to find an excel solution to match what out what sales a customer knows versus what we have in our system. I've searched this board but can't find anything similar. In the below example, sheet 1 would be what came out of our system, and sheet 2 would be what the customer has emailed....

View 11 Replies View Related

Creating Images Database Where Pics Are In Excel Database

Mar 31, 2004

I am currently trying to create a database of products for my company. For each product I would like to include an image associated with it. I then want to have on another sheet a place where the user will click an error and be able to cycle through the products. As tehy cycle the associated image will pop up.

What I need to understand is after importing the image into excel, how do I associate that image to a cell so I can reference it in another sheet of the database. I am not concerned with how large the database will get, my pictures are quite small.

View 4 Replies View Related

Database Named Database And Return A Time In Column

Nov 7, 2008

I'm trying to lookup a database named database and return a time in column A, based on criteria in cells a1 and b1 on another sheet, A1 would contain a number and B1 would contain a day from mon-fri

eg of Database
A B C D E F G
10:00 5000 Mon Tues

11:00 5000 Wed Thur Fri

and so on

if a1= 5000 & b1=Wed
how can I return 11:00

I have tried index and match =index(a:a,match(a1&b1,b:b&e:e,0))

View 9 Replies View Related

Select Active Row Plus Next 21 Rows Down?

Jul 17, 2014

I am trying to select the active row and the next 21 rows down, so in all 22 rows should be selected after the macro runs.

View 4 Replies View Related

Delete N Rows From Active Cell

Jun 12, 2009

Is there a quick way to delete "N" rows from the active cell regardless of the content of the cells in those rows? I import text files into excel then frequently have to go through them and delete rows of nonimportant data.

For example if cell A57 is currently selected can I run a macro that will delete the next 10 rows?

View 2 Replies View Related

Insert Rows At The Active Cell

Aug 7, 2006

I want to be able to allow a user to insert a number of rows (they would specify the number) into a worksheet, at the active cell location. I also need the same number of rows to be inserted into the same worksheet (but much lower down) The location of which is to be governed by the location of the original inserted rows.

Having created the new rows I need all the formulas from the row ABOVE the active cell to be copied into all the newly created rows.

example
If active cell is A5 I want user to be able to run a macro which asks them how many rows they wish to insert. (Assume the user asks for 7).

The macro then inserts that number of rows, (starting at row 5). And additionally adds 7 rows at a location further down the worksheet (say row 105). The formulas from the row above the active cell are then copied into all the newly created rows.

2nd example
If the active cell was A9 then the macro would add 7 rows, starting at row 9 and additionally add 7 rows starting at row 109. Then formulas added.

View 9 Replies View Related

Matching 2 Rows In 2 Different Sheets

Apr 28, 2009

I have 2 sheets with lots of data in them. On the first sheet in the A column i have data that looks like this: 01234567 On the second sheet in the D column i have data that looks like this: 1234567 It's basically the same data except without the 0 in front. The first sheet has like 60k rows and the second sheet has around 18k rows. I need to find out what data from the second sheet is in the first one. I'm attaching an example.

View 2 Replies View Related

Looking Up And Matching Rows To Columns?

Sep 17, 2013

I have a document where there are hundreds of rows and columns. The rows contain lines of data, while the columns are topics. Each row needs to be traced to an appropriate column. Once traced, I simply check off the appropriate cell with an X. I have to make sure that each row has at least one "X".

I have attached a sample file. look at cell B8. The statement in this row reads "Issue Placard information and registration". Now, I already know what column this statement belongs to (column CT).... but I don't want to scroll over hundreds of columns to that particular column, or have to freeze any panes, to locate that cell and mark it off. I have been doing this manually and it is incredibly tedious and time-consuming. Is there a way where I can locate the appropriate cell and go to it without having to scroll? When I'm scrolling I have to not only scroll, but I have to read the rows and columns again to make sure they indeed match. I want to be able to locate the right cell instantaneously and just enter an X value.

View 1 Replies View Related

Looking Up And Matching Rows To Columns

Nov 1, 2013

I have a document where there are hundreds of rows and columns. The rows contain lines of data, while the columns are topics. Each row needs to be traced to an appropriate column. Once traced, I simply check off the appropriate cell with an X. I have to make sure that each row has at least one "X".

I have attached a sample file. look at cell B8. The statement in this row reads "Issue Placard information and registration". Now, I already know what column this statement belongs to (column CT).... but I don't want to scroll over hundreds of columns to that particular column, or have to freeze any panes, to locate that cell and mark it off. I have been doing this manually and it is incredibly tedious and time-consuming. Is there a way where I can locate the appropriate cell and go to it without having to scroll? When I'm scrolling I have to not only scroll, but I have to read the rows and columns again to make sure they indeed match. I want to be able to locate the right cell instantaneously and just enter an X value.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved