Looking up and matching data

I have two sets of data with the same information but not in the same order 
and am trying to match the data.  In each data set I have 10 pools containing 
100 loans.  Each pool has a unique ID and each loan within the applicable 
pool has an ID of 1 to 100.  I need to look up the Pool ID, then look up the 
loan ID so that I can extract the property type information from a third 
column.  The Pool ID and property type is text but the loan ID is a number.

I am struggling to put together the right combination of formulas to give 
the property type for each loan within each pool.  Any suggestions would be 
greatly appreciated.
0
HRHRLBS (2)
5/6/2008 3:53:02 PM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
630 Views

Similar Articles

[PageSpeed] 57

Create a 'cheater' column in each of your 2 sets of data.
The cheater column combines the pool id and the loan id.
Ex: Assume pool id is in Col A and loan id is in Col B and the data starts 
in row 2.
     In your cheater column, in row 2 put the formula =A2 & B2
Now use a vlookup( ) formula to look up the property type info

Hope this helps,
Sincerely,
Gary Brown
 

"HRHRLBS" wrote:

> I have two sets of data with the same information but not in the same order 
> and am trying to match the data.  In each data set I have 10 pools containing 
> 100 loans.  Each pool has a unique ID and each loan within the applicable 
> pool has an ID of 1 to 100.  I need to look up the Pool ID, then look up the 
> loan ID so that I can extract the property type information from a third 
> column.  The Pool ID and property type is text but the loan ID is a number.
> 
> I am struggling to put together the right combination of formulas to give 
> the property type for each loan within each pool.  Any suggestions would be 
> greatly appreciated.
0
Brown (37)
5/6/2008 5:26:02 PM
Gary,

Thank you - very helpful.  Now if I can just figure out why I get #Value! 
result when I combine the two columns I can move ahead.

"Gary Brown" wrote:

> Create a 'cheater' column in each of your 2 sets of data.
> The cheater column combines the pool id and the loan id.
> Ex: Assume pool id is in Col A and loan id is in Col B and the data starts 
> in row 2.
>      In your cheater column, in row 2 put the formula =A2 & B2
> Now use a vlookup( ) formula to look up the property type info
> 
> Hope this helps,
> Sincerely,
> Gary Brown
>  
> 
> "HRHRLBS" wrote:
> 
> > I have two sets of data with the same information but not in the same order 
> > and am trying to match the data.  In each data set I have 10 pools containing 
> > 100 loans.  Each pool has a unique ID and each loan within the applicable 
> > pool has an ID of 1 to 100.  I need to look up the Pool ID, then look up the 
> > loan ID so that I can extract the property type information from a third 
> > column.  The Pool ID and property type is text but the loan ID is a number.
> > 
> > I am struggling to put together the right combination of formulas to give 
> > the property type for each loan within each pool.  Any suggestions would be 
> > greatly appreciated.
0
HRHRLBS (2)
5/7/2008 6:19:27 PM
Reply:

Similar Artilces:

In an excel chart I want missing data to be ignored
I have several graphs I want to generate from a table of data. Some of the data is missing for certain parameters. Excel will not join points that are not part of a continuous set of data date Na conc. Cl conc 1/3/08 13 6 1/4/08 5 1/5/08 8 1/6/08 4 3 Any ideas Hi, If the cell is truly empty and the line drops to zero select the chart and check, Tools > Options > Chart > Plot empty cells as... If the cell actually contain conditional fo...

All my icons look the same (except IE8 and the recycle bin)
i cant open any windows...when i click on them the open with window pops up and wants me to pick wat i want to open it with...i can only get on the internet in safe mode with networking. and when i try to open...for example paint it says invalid bitmap, or its format is not currently supported and other windows wont even open. got any ideas on how i can fix this?? On Sun, 30 May 2010 06:54:01 -0700, xxkoreanxx wrote in message <news:999F2CE9-8E21-47DC-8BA4-BAC1F1329F6E@microsoft.com>: > i cant open any windows...when i click on them the open with window pops up > a...

How to create data connections for tab-delimited data...
Hi, I am having difficulty with creating a tab-delimited data connection. I used the Excel Import Data Wizard with success. When I try to access the Data Connection to change my source file, for some reason I don't have access to the Properties Tab. Can you help explain what I am doing wrong? Thanks in advance for your assistance. -Greg ...

Looking for feedback
Hey everyone just started my own business doing consulting / training for Microsoft Outlook please check out my site www.outlookchallenged.com and provide any suggestions / feedback please. Any advice is good advice at this point. Thanks -- www.outlookchallenged.com Br Sense <outlookhelp@ftard.com> wrote: > Hey everyone just started my own business doing consulting / training > for Microsoft Outlook please check out my site > www.outlookchallenged.com and provide any suggestions / feedback > please. Any advice is good advice at this point. The best advice I have to offer ...

Looking for CRM Solution
Does anyone use, or has anyone encountered, a CRM based solution used for residentail, multifamily property management? On Apr 28, 12:55=A0pm, MrB <M...@discussions.microsoft.com> wrote: > Does anyone use, or has anyone encountered, a CRM based solution used for > residentail, multifamily property management? The beauty of Microsoft CRM is that it can be customized for any business in any industry. It's ability to be customized using front- end tools (not within code) is , as far as I know, unparalleled by any other software package. You have the ability to create new entiti...

match #2
This is complicated as I probably have not expained myself propery bu here goes: I have a fixture list (home team v away team) and two sets of leagu tables (home statistics and away statistics) I aquire this data fro the web using web query I would like to match the data from the leagu table to the fixture list of the corresponding teams (which I can d manually) but I would like to do this automatically.The problem I hav is that if I try to match the team in the fixture list with it corresponding data in the home or away table I am unable to do this a the team name don't quite match as the...

export data from excel to adobe
If you have 2007, Microsoft has a free add-in that will add the ability to print to a file in .pdf format that works across all Office 2007 applications: http://www.microsoft.com/downloads/details.aspx?FamilyId=4D951911-3E7E-4AE6-B059-A2E79ED87041&displaylang=en If you don't have 2007, then one of the easiest to use .pdf file creators is probably PDFCreator from SourceForge: http://sourceforge.net/projects/pdfcreator/ it installs as a virtual printer on your system and when you wish to create a .pdf file you simply print to that virtual printer. Instead of a sheet of paper, y...

missing data after upgrade
I am using Windows XP. I upgraded from Office 2000 to Office 2003 and my e-mails in Outlook have gone missing. I searched my hard disk for *.pst files (including hidden files) but only one showed up which seems to be the new (empty) one. How do I get back my old e-mails? What size is the pst? "KOC" <anonymous@discussions.microsoft.com> wrote in message news:5e7101c49265$02fb1ca0$a501280a@phx.gbl... > I am using Windows XP. I upgraded from Office 2000 to > Office 2003 and my e-mails in Outlook have gone missing. > > I searched my hard disk for *.pst files (i...

Cannot edit Excel data in a PowerPoint
I'm not sure which discussion group this applies to, so I'm posting it in both Excel and PowerPoint. I just started having problems with any Excel workbooks embedded into a PowerPoint presentation. It is a bit difficult to describe but I'll give it my best shot. Whenever I double-click an Excel object to edit the data, it opens up like normal and gives me all of the MS Excel options I normally get. But what is very VERY strange is that when I try to select a single cell (or row/column), it will take me into the MS Excel program and not allow me to edit anything ...

Data Error in MS Money 2002
I've been using Money 02 for yrs now and have always imported data from my bank's website. I just recently tried to import my monthly data and received the following msg, "The file you attempted to import appears to be invalid or contains corrupt data. Please download the file again. If this problem continues, contact your financial institution." I dbl checked format and called bank's IT support 2 times but no issues reported from their end. Is this a Money problem and has anyone experienced same? Please help. Thanks - Brian In microsoft.public.money, Bri...

=COUNTA() with multiple look-up
Hi All, I would like to count the number of items that have a value "AAA" in column A and an amount >0 in column B. As I am not quite sure how to incorporate two look-ups in a counta formula I was wondering if anyone can give me the formula? Many thanks! Rgds, Robert Try something like this: =SUMPRODUCT((A1:A100="AAA")*(B1:B100>0)) or....if there may be some text cells in B1:B100 interspersed with the numbers =SUMPRODUCT((A1:A100="AAA")*ISNUMBER(B1:B100)*(B1:B100>0)) Is that something you can work with? *********** Regards, Ron XL2002, WinXP &...

can I edit a picture to look faded?
I want to edit my picture to be faded so I can use it as background with text over it. "deborahm" <deborahm@discussions.microsoft.com> wrote in message news:4D93DD63-8553-447D-A0AC-44EE0E1BD9B3@microsoft.com... > I want to edit my picture to be faded so I can use it as background with > text > over it. In Publisher you can do it one of two ways. For both ways start by inserting the picture. Method 1, right click on picture and select Format Picture, click on picture tab, then click on the dropdown for Color under Image Control and select wash...

Formulas in source data
I have a chart with twelve months of data that is generated by using a fomula: Cell A1 has the following: =IF(A2>5,A2*A3,"") Cell B1 contains: =IF(B2>5,B2*B3,"") These fomulas go out for twelve cells and are the source data for my line chart. I have only the first six cells with data in them and when I set up my chart I get the first six data points plus six data points at the zero value of the Y-axis. Can I suppress the data points where the formula returns a null value? TIA. You can use the NA() function to do this. "Ken" <Ken@discussions.micro...

Change cells with same data
Hello Everybody, I was just wondering if there was a way in excel (office xp), were I could change cells that all have the same data, maybe number or text(like a word). At the same time. If there was a command or function that would go through them all and change it if i changed one of them. something like that. Thanks in advanced. You could try Find+Replace (Ctrl+H). You tell the system what to find and what to replace it with, click Replace All and it's done! HTH, Steve "MtK" <kamachi923@hotmail.com> wrote in message news:2565ad7.0312081006.506c6801@posting.google.co...

data look up and return values across a row
I have a list in one spreadsheet, we'll call it 'spreadsheet A' that I need to cross reference with another larger spreadsheet, which we'll call 'spreadsheet B'. I'd like to remove everything from B that isn't on A. Is there an easy way of doing that? Chip Pearson has lots of info about working with duplicates at: http://www.cpearson.com/excel/duplicat.htm Betsey wrote: > > I have a list in one spreadsheet, we'll call it 'spreadsheet A' that I need > to cross reference with another larger spreadsheet, which we'll call > &#...

Loosing Data after closing Page
I've used FrontPage successfully for years but a few weeks ago I started to design a new web page. I put in my background, picture and text boxes. When I was happy with the look I closed the page. The next day I opened it to make some changes and all my text was gone. The background and photo remained but everything else was gone. I've tried everything I could think of; Detect & Repair, opening an old web page and renaming it, but the same thing happens. I ended up using Publisher to make a mock up to submit for approval. I've got 4 days to have this site ...

How do I copy a graph to new worksheet and get data from new sheet?
When I copy a worksheet with a graph or just copy a graph and paste in a new worksheet, the source data is still linked to the original worksheet. I've tried editing the sorce data to remove the sheet name to try to make it relative to the current sheet, but that gives me "Error in your formula". Is there a quick way to get the graph to link to the data in the new worksheet? Even a macro would help, if that's what it takes. Thanx in advance... If the chart is embedded in the worksheet, you should be able to copy the worksheet elsewhere, and the copied chart will point t...

Looking for basic multiplaction formula
HI ALL, I'm trying to multiply cells d5-d9 by cells a5-a9 and put the answer in cells d14 - d18 ( using caps ) I have tried =(D5*A5) in d14 and then pulling it down to d18 PLEASE HELP ME IVE BEEN AT THIS FOR AN HOUR AND A HALF!! Sounds to me like you had it right... what's not working? In cell D14 enter "=D5*A5" Copy that and paste it in cells D15 to D18. Ken Puls, CMA - Microsoft MVP (Excel) www.excelguru.ca Concarp wrote: > HI ALL, > I'm trying to multiply cells d5-d9 by cells a5-a9 and > put the answer in cells d14 - d18 ( using caps ) > ...

matching unequal text fields
I want to find records that contain disparent records with data in comparable fields like this: Data in Table 1 = BRG, ROLLER, 6020, FLANDERS, ER53 Data in Table 2 = BRG, ROLLER, 6020, FLANDER, ER53, 6.22X5.11, OD 5" Notes: the data layout is not standard for all records (i.e. the commas, naming convention are not universal, so we can't just strip-out the data). I'm not sure what you want to compare between the two fields? Which text from Table 1 is to match which text in Table 2? Show specific examples, please, and provide table and field names so that we can ta...

Transactions no longer auto-matching
I have Money 2004 SB on XP Pro. My downloaded transactions no longer automatically match to existing transations. How do I get that back? Take a look at the heading "Downloading Statements" under Tools -> Options -> Online Services -Gary <anonymous@discussions.microsoft.com> wrote in message news:2a69901c465bc$b88aaed0$a301280a@phx.gbl... I have Money 2004 SB on XP Pro. My downloaded transactions no longer automatically match to existing transations. How do I get that back? ...

Graph Source Data
I have a cell A1 which consists of value : 'Data'!$A$1:$B$4 I want to create a graph based on that cell. Can I use indirect for this purpose ? Thanks Just highlight the range and name it mygraphrng or something then goto the graph wizard and where it asks for source data type in sheet12!mygraphrng modify to suit -- Don Guillett SalesAid Software donaldb@281.com "diego" <porno_abis@hotmail.com> wrote in message news:00c401c3b2b2$91237a10$a401280a@phx.gbl... > > I have a cell A1 which consists of value : 'Data'!$A$1:$B$4 > > I want to create...

Matching data
Hello all The macro below will match data in sheet1 with that in Sheet2 and when found copy three cells from the first sheet to the other. The problem I have is when more then one match is made in that it overwrites the previous data rather then adding to it. I would be obliged for your help. TIA Karen Sub Match() Dim lookupRange As Range Dim myRange As Range Dim cell As Range Dim myRow As Variant Set lookupRange = Sheets("Sheet1").Range("C:C") With Sheets("Sheet2") Set myRange = .Range("B2:B" & .Range("B" & Rows.Count).End(xlUp)....

Looking for sheet to handle MPG, etc
HI I am looking for a sheet to handle gas mileage. things like MPG etc. It would be great to have a ready made sheet to handle this info thanks supermari -- Message posted from http://www.ExcelForum.com This shouldn't be too difficult unless you attended college at Texas A&M =miles driven/gallons used -- Don Guillett SalesAid Software donaldb@281.com "supermario >" <<supermario.1a3sk4@excelforum-nospam.com> wrote in message news:supermario.1a3sk4@excelforum-nospam.com... > HI > I am looking for a sheet to handle gas mileage. > things like MPG etc. &g...

look up who is connected
hello i would like to check if any pc connects to my xp box which it shares a lot of dirs. can powershell do the job and how? not sure in PS but "netstat" could do this for you. regards Ramazan "ryan" <ryanlihk@hotmail.com> wrote in message news:eKobVuVrKHA.4220@TK2MSFTNGP05.phx.gbl... > hello > > i would like to check if any pc connects to my xp box which it shares a > lot of dirs. > > can powershell do the job and how? thanks On 2/15/2010 2:21 AM, RCan wrote: > not sure in PS but "netstat" could do this...

Report data truncated on Access 2007
Hi. I have an application in Access 2003 that creates reports dinamically, and asigns font sizes for the data to be print. When I run the application in Access 2007, the font's size for some fields are changed to 11 instead of 8, and hence the data on the reports get truncated. Any ideas how to solve this issue? Thanks. The simplest way is to make a version that only runs in 2007, correcting those controls where the font size goes awry. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "alexrs2k" <alexr...