Re: data return based on two criteria #2

Hello,

Can I use the same formula for the below requirement?
Sheet1
Column A - Assembly numbers (numbers repeats)
Column B - prepart (numbers repeats)
Column C - prepart description (description repeats)
Sheet 2
Column A - assembly partnumber
Column b1 : Z1 - prepart description

I would like to return corresponding prepart number
Eg.
Data base

A		B		C	

X		123		aaaaaa
Z		123		aaaaaa
A1		F002		aaaaaa
Z2		F002		aaaaaa
C3		456		bbbbbb
D2		856		cccccc
F2		589		dddddd

Result

    	A		B		C		d
1	Assembly	aaaaaa	bbbbbb	cccccc
2	X		123
3	Z		123
4	A1		F002
5	Z2		F002
6	C3				456
7	D2						856

Regards

Jimmy Joseph
0
6/10/2006 10:39:35 AM
excel 39879 articles. 2 followers. Follow

2 Replies
493 Views

Similar Articles

[PageSpeed] 34

Chip Pearson has a way of extracting an arbitrary value with repeats:
http://cpearson.com/excel/lookups.htm

Look for "Arbitrary Lookups"



Jimmy Joseph wrote:
> 
> Hello,
> 
> Can I use the same formula for the below requirement?
> Sheet1
> Column A - Assembly numbers (numbers repeats)
> Column B - prepart (numbers repeats)
> Column C - prepart description (description repeats)
> Sheet 2
> Column A - assembly partnumber
> Column b1 : Z1 - prepart description
> 
> I would like to return corresponding prepart number
> Eg.
> Data base
> 
> A               B               C
> 
> X               123             aaaaaa
> Z               123             aaaaaa
> A1              F002            aaaaaa
> Z2              F002            aaaaaa
> C3              456             bbbbbb
> D2              856             cccccc
> F2              589             dddddd
> 
> Result
> 
>         A               B               C               d
> 1       Assembly        aaaaaa  bbbbbb  cccccc
> 2       X               123
> 3       Z               123
> 4       A1              F002
> 5       Z2              F002
> 6       C3                              456
> 7       D2                                              856
> 
> Regards
> 
> Jimmy Joseph

-- 

Dave Peterson
0
petersod (12004)
6/10/2006 11:16:31 AM
I think I misunderstood.

The values in column A repeat as well as the values in column B and C.

But there are never multiple instances of the same set of 3 values.

With your value in A2 and C1:

=index(othersheet!$b$1:$b$100,
   match(1,($a1=othersheet!$a$1:$a$100)
          *(c$1=othersheet!$c$1:$c$100),0))

(all in one cell)

This is an array formula.  Hit ctrl-shift-enter instead of enter.  If you do it
correctly, excel will wrap curly brackets {} around your formula.  (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.


You can ignore the error if there is no match or convert to values and
edit|replace to get rid of it or even hide it using conditional formatting.

Or modify the formula to check to see if there's a match.

===
I think that's what you meant....

Jimmy Joseph wrote:
> 
> Hello,
> 
> Can I use the same formula for the below requirement?
> Sheet1
> Column A - Assembly numbers (numbers repeats)
> Column B - prepart (numbers repeats)
> Column C - prepart description (description repeats)
> Sheet 2
> Column A - assembly partnumber
> Column b1 : Z1 - prepart description
> 
> I would like to return corresponding prepart number
> Eg.
> Data base
> 
> A               B               C
> 
> X               123             aaaaaa
> Z               123             aaaaaa
> A1              F002            aaaaaa
> Z2              F002            aaaaaa
> C3              456             bbbbbb
> D2              856             cccccc
> F2              589             dddddd
> 
> Result
> 
>         A               B               C               d
> 1       Assembly        aaaaaa  bbbbbb  cccccc
> 2       X               123
> 3       Z               123
> 4       A1              F002
> 5       Z2              F002
> 6       C3                              456
> 7       D2                                              856
> 
> Regards
> 
> Jimmy Joseph

-- 

Dave Peterson
0
petersod (12004)
6/10/2006 11:34:59 AM
Reply:

Similar Artilces:

Data Migration #4
Hi, i can't migrate from sage line 50 into GP 10.o -- Microsoft Dyanmics GP 10 TBash: Did you have a question or just wanted to make that comment? Frank Hamelly MCP-GP, MCT East Coast Dynamics www.eastcoast-dynamics.com i am asking for solutions on Migration from third party products to GP (Sage Line50,Line 500, Peachtree and Simply Accounting) -- Microsoft Dyanmics GP 10 "Frank Hamelly, MCP-GP" wrote: > TBash: > > Did you have a question or just wanted to make that comment? > > Frank Hamelly > MCP-GP, MCT > East Coast Dynamics > www.eastcoast-...

Re-arranging table using pivot-table?
I have a worksheet with data organized somewhat like this: New York New York New York Albany Florida Miami Florida Orlando Florida Tampa Florida Jacksonville California Los Angeles California San Diego I need to convert this to a list of states and each city in the columns to the right of their corresponding state. Something like this: New York New York Albany Florida Miami Orlando Tampa Jacksonville California Los Angeles San Diego Any help is greatly appreci...

Re-install Outlook 2002
I am trying to re-install Outlook 2002 for my palm pilot after a crash. The installation will run until I get the message - "another version is already installed and must be uninstalled". The previous version was corrupt and I was unable to uninstall it. Now when I go into the control panel to add/remove the uninstall is no longer in the listed. Help! ...

http virtual server error #2
We have an exchange 2003 sp2 setup on a cluster. anytime the server fails over the IIS host headers for the http virtual server are re stamped. We found the kb article here http://support.microsoft.com/default.aspx?scid=KB;en-us;287726 This worked beautifully on one of the clusters, but the other two give me error messages when attemping to go to the http virtual server properties via ESM. The error is: "Exchange System Manager" An unexpected error occured ID no: 8000ffff Exchange System Manager When you click ok, the properties do come up but the general tab is a...

2 or more columns on a report
I have a report that has only a couple of fields that it's pulling from, so when I print my report, there's not a lot there. It looks basically like this: Employee Name Avg. Score # Completed Doe, John 95 2 Smith, Mary 68 3 and so on. My question is this: Is there a way to put 2 groups of this info on the report page to condense the number of pages I have to print out? I'd like to report to look something like this: Employee name Avg. Score # Completed Employee Name Avg. Score # Completed ...

Macro referencing data validation doesn't work
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel Hi - New to VBA &amp; the forum. <br><br>When I create a macro that references a data validation cell, the code does not acknowledge the value selected from the pull down menu. But, if the value is manually typed into the data validation cell, the code works fine. Any ideas? I've had PC users try the code with no problems. <br><br>I'm using the following code: <br> [Code]Private Sub Worksheet_Change(ByVal Target As Range) <br> If Not Intersect(Target, Range(&qu...

Cell formatting when importing data
When I import data from a delimited file into Excel 2000, even though I have pre-formatted a column as text, entries such as 12-5 and 1-4 -- which are not dates display as dates. When I reapply the text formatting I get a number: 38691. How can I get 12-5 to display as is when I bring the data in? Rename the file to *.txt and use the text import wizard, just click next twice and in step 3 select text from column data format for this particular column and click finish -- Regards, Peo Sjoblom "cjay" <cjay@discussions.microsoft.com> wrote in message news:90AAB750-BF9F...

Pivot Table #2
I have a database with type and age ranges in columns. Six age ranges. One type column. The data (dollars), by type and age range are below each age range column. I want a summary report that sums the dollars by type and age combined. My preference is to have the sum of age ranges in the rows, the type in the columns with the dollar sum of each type and age range in the data fields. I hope to have the sum of the types for all ages on the right and the sum of the ages for all types at the bottom. It seems simple enough but, I don't seem to be able to make the table work? If ...

Re-Enable CRM User
Hello Everyone, I am trying to enable a disabled CRM user and I received the following error message. Any thoughts or suggestions would be greatly appreciated. [COMException (0x80040204)] Microsoft.Crm.Platform.ComProxy.CBizUserClass.Enable(CUserAuth& Caller, String UserId) +0 Microsoft.Crm.Application.Platform.SystemUser.Activate(String id) +38 Microsoft.Crm.Web.BusinessManagement.SystemUserDetailPage.changeState(Object sender, DataEventArgs e) +127 Microsoft.Crm.Application.Forms.DataEventProcessor.Raise(FormEventId eventId, FormState state, User user, String objectId,...

If Function help #2
I have what should be a simple IF then formula, which is copied below, where both criteria must be met: Z9 must be true and S9 must = COS. Any assistance why this isn't working would be greatly appreciated. =IF(AND(Z9=TRUE,S9="COS"),B9,"N/A") Thanks! Hi What is contained in Z9? Maybe you need =IF(AND(Z9,S9="COS"),B9,"N/A") -- Regards Roger Govier <mansfieldtw@gmail.com> wrote in message news:1178309371.607676.90090@n59g2000hsh.googlegroups.com... >I have what should be a simple IF then formula, which is copied below, > where b...

RE:Excel will not open from a web link
When opening a link on a web page that has an Excel Spreadsheet embedded in it, the spreadsheet will not open up Excel but opens within another browser as an Excel file. Programming on webpage is correct, opens up on other machines just fine. ...

Sorting data problem
Dear all I have a sheet where i retrieve data from a database in another sheet i have links to the first sheet where i brink the data from the database in more formal format and grouping the companies by division In this sheet i have four columns first the nmae of the companie, the second ithe last year income the third is the budget and the forth is the actual income I would like to sort the data by the forth column in asceding way I tried to do it but there was a problem with the link and in the other hand when i was asking the asceding way the was sort only by the fourth column and there ...

Add / to data
I have data like 02042007 I would like to update it so it looks like 02/04/2007 how can I update this with a query? Do you want to UPDATE the field - permanently replace the value or do you just want to change the way the data is displayed? Assumption: the value is a string. To change the display without changing the data FORMAT([DataValue],"@@/@@/@@@@") Of course if the data is not exactly 8 characters in length you will get some unexpected results To permanently change the data use an update query where you set the field to the expression above. To change the datatype, pos...

I need a macro to find cut and paste data to new cell
I have data that I have exported to excel that I need to reformat to be able to create a pivot table to check for duplicate entries. On importing the data which is in the form of journal entries the memo line is stting above the journal numbers in column D. I need to find all of the comments and cut and paste them to column C. The comments are not all the same but do contain the same word "To" in the comment. The journals are not all the same size and so the comment line does not appear at regular intervals. Any help appreciated as I am very new to VBA ...

Business Portal 2.7 and eReq (print a PO, display GL account descr
Has anybody tried diplaying the GL acct description when you choose to viiew or edit a doc in eReg. Also, has anbody been able to print or display the PO that gets creaed in eReq from the eReq module without having to log inot Great Plains to get access to it. thks david ...

Thanks Ken Wright but one more question RE saving data from circular references
Thanks heaps for that macro Ken Wright . I have run it and it works. However it loops forever. Can I set it up so that it could save dat from a certain number of iterations? Thanks again Michae -- Message posted from http://www.ExcelForum.com Hi Michael, If you stayed within your thread and did not change the subject Ken would see your reply as a reply to his reply and would see it a lot faster than when you start a new thread. Ken would probably see your post highlighted in RED. Also someone other than Ken might have been able to help you. (not me I avoid anything that hints of circul...

excel.exe has generated errors #2
Hello, Sorry for the cross post. I am receiving the following message: ""Excel.exe has generated errors and will be closed by Windows.You will need to restart the program An error log is being created."" when trying to open xls files that have been copied from another computer. Both Excel versions are the same and the file works on the source machine. I am copying it to and from a floppy which also works when opened on the source machine. Any ideas will be greatly appreciated. Thanks in advance. -- Mrich -- Mrich Have you copied the file from the floppy to your hard...

Criteria for opening a form based on a value in another form's subform
Hello everyone, Please pardon the noob question, but I've got a form with a subform and I would like to be able to open another form with only the records that match the primary key from the first form's subform, but I am unable to figure out how to specify that value in the query. In other forms (where subforms are not invovled), I can accomplish this with something like: =[Forms]![Form_Name].[Desired_Value] Is there something different I need to be doing when attempting to get a similar value from a subform? With many thanks in advance for any advice... -- Message posted via h...

Re: exmerge error
I am getting the following error when trying to use exmerg can anyone please help *************************************************************** Microsoft Exchange Mailbox Merge Program, v4.00.02 Start Logging:February 27, 2004 20:04:3 *************************************************************** [20:04:31] Logging Level: Non [20:04:31] Reading settings from file 'C:\Program Files\Exchsrvr\BIN\EXMERGE.INI' [20:04:32] Error 8007203a opening an LDAP connection. ('LDAP://EXCHANGE/rootDSE') (CADRoutines::GetNamingContextData [20:04:32] Accessing Domain Controller 'PROFI...

Act -> CRM 1.2
Hi all. I know just about nothing on the subject of importing act data into CRM 1.2 Could any one help me out with a quick overview of the process? I know about using export pro to get the data into access. I think from there you simply use the data import tool that MS provides from crm. MAIN QUESTION: Can I import act data once CRM is up and running? I really do hope so. I'd hate to find out that we needed to create the database 1st and then select "connect to existing database" during the install process. Thanks in advance!!!! Hank wrote: > Hi all. > I know just...

LIST option missing in DATA Menu
A user has the LIST option missing in the DATA submenu of Excel 2003. What would cause this and how do I help her correct it -- cccgsmit ----------------------------------------------------------------------- cccgsmith's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1524 View this thread: http://www.excelforum.com/showthread.php?threadid=26873 Perhaps she has short menus enabled. To change this -- Choose Tools>Customize Select the Options tab Add a check mark to 'Always show full menus' Click OK Or, to use short menus, and see commands that haven&...

Ron Bovey re: "Trim Selection" in ExcelUtilities
Cells in my Excel spreadsheet appear to be empty. However, when I use Edit/Go To/Special/Blanks, I get "No cells were found". In the past, Ron Bovey suggested going to the WWW.APPROS.COM website, download ExcelUtilities and then use the "Trim Selection" utility to remove the characters that are making the cells not empty. Since then, I've gone to the WWW.APPROS.COM website and downloaded the ASAP Utilities. However, I can seem to find the "Trim Selection" nor any utility that will do the same thing. Help! Try going to Rob (not Ron) Bovey's site: ...

Open a Report with two different forms
I have a report (rptRepairRequest) that I have command button that prints it out from a form based on the ID of that form. This works fine. What I would like, and not sure if it is possible, is to have another form where I can just select the ID from a drop-down and open the same report. My record source for the report is currently: SELECT tblRepairList.* FROM tblRepairList WHERE (((tblRepairList.RepairID)=Forms!frmSerReqEntry!txtRepairID)); I tried adding an OR statement at the end: "OR (tblRepairList.RepairID)=Forms![frmRptRepairRequest]! [cboRepairID]" but it asks me for '...

Outlook 2000 won't remember password #2
I had to reinstall office onto an XP machine, and Outlook 2000 won't remember the account password. Yes... I have checked the box that says "remember password." Any ideas? Is this a known problem? Any fixes? Pete <Pete@nospam.com> wrote: > I had to reinstall office onto an XP machine, and Outlook 2000 won't > remember the account password. Yes... I have checked the box that says > "remember password." Any ideas? Is this a known problem? Any fixes? <http://support.microsoft.com/search/default.aspx?catalog=LCID%3D1033&1033comm=1&spid=2557&...

How do I get invisible lines & borders to re-appear?
In Publisher 2003, table grid lines, text box borders and autoshape borders have become invisible on-screen, although they do print out. Any ideas on how to remedy this? Recently installed Adobe PDF Maker which seems to have precipitated this, although it could be entirely co-incidental. Try updating your video drivers. -- JoAnn Paules MVP Microsoft [Publisher] "Ackerman & Co" <Ackerman & Co@discussions.microsoft.com> wrote in message news:0FFE40C2-D6A8-43AE-89E4-061EBB6501DB@microsoft.com... > In Publisher 2003, table grid lines, text box borders and auto...