Lookup and fetch from the same column

I have spread sheet that has three columns, out of which two are pr
populated and I am finding the information for the third column fro
the other system.
> 
> Example :
> Module--	Job----------  Owner
> abc215--	AQ20DX	
> abc224--	ABG9DA	
> abc224--	ABG9DB	
> abc224--	ABG9DX	
> abc225--	AO14DX	
> abc225--	AO15DX	
> abc375--	AQ20DX	
> abf987--	ABG9DA	
> abh897--	ABG9DB	
> 

Using the Job Name I get the information from the other system an
populate the info as follows.

> 
> Module--	Job-------Owner
> abc215--	AQ20DX--14B
> abc224--	ABG9DA--14E
> abc224--	ABG9DB--14D
> abc224--	ABG9DX--14E
> abc225--	AO14DX--14E
> abc225--	AO15DX--14E
> abc375--	AQ20DX	
> abf987--	ABG9DA	
> abh897--	ABG9DB	
> 
> 

If you notice the last three rows of job names are nothing but th
first three rows. so the owner information for these three jobs als
same as the first three jobs. It can happen many times since man
programs can be executed by same jobs. 

Now my requirement is: If the same job is repeated , can I have formul
to fetch the owner information and populate it on the owner column.

Now in this case, I would expect that the last three rows of the owne
information should be auto populated as follows since the owne
information is already identified above.

> 
> Module--	Job-------Owner
> abc215--	AQ20DX--14B
> abc224--	ABG9DA--14E
> abc224--	ABG9DB--14D
> abc224--	ABG9DX--14E
> abc225--	AO14DX--14E
> abc225--	AO15DX--14E
> abc375--	AQ20DX--14B  <== Auto populated	
> abf987--	ABG9DA--14E  <== Auto populated	
> abh897--	ABG9DB--14D  <== Auto Populated	
> 
> 


Can some one help me in this 

--
Dhruva10
-----------------------------------------------------------------------
Dhruva101's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3707
View this thread: http://www.excelforum.com/showthread.php?threadid=57024

0
8/10/2006 6:36:23 AM
excel 39879 articles. 2 followers. Follow

1 Replies
390 Views

Similar Articles

[PageSpeed] 15

How about, in the third row

=IF(NOT(ISNA(VLOOKUP(B3,$B$2:$C2,2,False))),VLOOKUP(B3,$B$2:$C2,2,False),"")

and copy down

-- 
 HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Dhruva101" <Dhruva101.2cba8p_1155192003.5048@excelforum-nospam.com> wrote
in message news:Dhruva101.2cba8p_1155192003.5048@excelforum-nospam.com...
>
> I have spread sheet that has three columns, out of which two are pre
> populated and I am finding the information for the third column from
> the other system.
> >
> > Example :
> > Module-- Job----------  Owner
> > abc215-- AQ20DX
> > abc224-- ABG9DA
> > abc224-- ABG9DB
> > abc224-- ABG9DX
> > abc225-- AO14DX
> > abc225-- AO15DX
> > abc375-- AQ20DX
> > abf987-- ABG9DA
> > abh897-- ABG9DB
> >
>
> Using the Job Name I get the information from the other system and
> populate the info as follows.
>
> >
> > Module-- Job-------Owner
> > abc215-- AQ20DX--14B
> > abc224-- ABG9DA--14E
> > abc224-- ABG9DB--14D
> > abc224-- ABG9DX--14E
> > abc225-- AO14DX--14E
> > abc225-- AO15DX--14E
> > abc375-- AQ20DX
> > abf987-- ABG9DA
> > abh897-- ABG9DB
> >
> >
>
> If you notice the last three rows of job names are nothing but the
> first three rows. so the owner information for these three jobs also
> same as the first three jobs. It can happen many times since many
> programs can be executed by same jobs.
>
> Now my requirement is: If the same job is repeated , can I have formula
> to fetch the owner information and populate it on the owner column.
>
> Now in this case, I would expect that the last three rows of the owner
> information should be auto populated as follows since the owner
> information is already identified above.
>
> >
> > Module-- Job-------Owner
> > abc215-- AQ20DX--14B
> > abc224-- ABG9DA--14E
> > abc224-- ABG9DB--14D
> > abc224-- ABG9DX--14E
> > abc225-- AO14DX--14E
> > abc225-- AO15DX--14E
> > abc375-- AQ20DX--14B  <== Auto populated
> > abf987-- ABG9DA--14E  <== Auto populated
> > abh897-- ABG9DB--14D  <== Auto Populated
> >
> >
>
>
> Can some one help me in this ?
>
>
> -- 
> Dhruva101
> ------------------------------------------------------------------------
> Dhruva101's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=37074
> View this thread: http://www.excelforum.com/showthread.php?threadid=570240
>


0
bob.NGs1 (1661)
8/10/2006 8:53:35 AM
Reply:

Similar Artilces:

how do I insert column headings or labels without using a row?
"column headings" <column headings@discussions.microsoft.com> wrote in message news:393A9243-0620-480E-A43C-03C3234867EE@microsoft.com... > You don't. Excel has plenty of rows - using just one isn't a problem! Thanks.. But then the row numbering is "taken up" by the title and the "first" row of data is numbered 2. Is there a way to avoid this? Also, how do I designate this first row as a "Column Heading" so that it reprints on every page? "Stephen" wrote: > "column headings" <column headings@discuss...

filtering columns-left nav
I am trying to locate the functionality for the filtering of columns on the left hand side of the screen within the nav area - that allows for totals to be displayed and summed based on the set markers. ...

how can i save mail a folder column view?
i have to open each mail address folder (using multiple email addresses and sub folders) everytime i start outlook--how can i save a current folder view as the default with all folders opened? ...

Table relationships and lookups
Hi guys, I may be a little over my head, I've had some experience in creating simple access db's. however this one will be extremely complicated as far as I can tell. Some backround info - i've got an excel spreadsheet currently that i would like to convert to Access. The spreadsheet does multiple lookups and calucations. This is for a Soccer club that i run to maintain roster information, dollars, scheduling and stats. I'm currently working on the scheduling pience. Here's what I have so far. tables. Club - Lists the teams in the club, home field name and ...

Renaming columns #2
I'd like to rename the A B C D at the top to the name of my columns, o at least leave the column headings at the top of my screen. Does anyone know how -- Message posted from http://www.ExcelForum.com Tools>Options>View, uncheck "Row and column headers". You can insert a row and a column with your own names, but they will not be recognized in formulas the same way as "A1". But you can define row and column names and use the intersection as an address in a formula. The intersection operator is a space. So if you defined the name "Material" for column ...

Invoice lookup by paid check number
I frequently have vendors call me asking for information about what invoices were paid by a check number. Is ther an easy way to look this information up? -- Rodger You could go to Inquiry>Purchasing>Transactions by Document, put your check number in the 'from' and 'to' fields. Once the document is displayed in the scrolling window zoom back on the 'Unapplied Amount' field. Viola! the documents paid by the selected payment are listed. Unfortunately there isn't a print icon on this inquiry window, but I think it's the information you wanted. &quo...

Catagorize Column Data by Name
I have a portfolio spreadsheet with approx 100-200 rows where each row depicts a unique project and each column depicts a calendar week. The cells in each row are color coordinated to illustrate the phases of the project. (For instance, I could have 3 consecutive tan cells illustrating analysis, 5 consecutive "no fill" cells depicting development, and 4 gray cells indicating testing. It is actually a bit more colorful but I will keep it simple for now!) In each cell there is a numeric value describing how many projected resource hours are to be applied to that phase for th...

Lookup
Q103 Q102 Q202 Q302 Q402 Q103 Q203 How can I lookup the Q103 in the row above and then have it pull the number to the right one cell (Q203)? thanks If I understand correctly =INDEX(A2:F2,MATCH(A1,A2:F2,0)+1) where q103 is in a1 and q102-q203 is in a2-f2 Lance >-----Original Message----- >Q103 > > >Q102 Q202 Q302 Q402 Q103 Q203 > > >How can I lookup the Q103 in the row above and then have >it pull the number to the right one cell (Q203)? thanks >. > matt wrote: > Q103 > > > Q102 Q202 Q302 Q402 Q...

rows & columns height & width
Hello, Is there a way of forcing the row height and column width to be equal , and to remain equal, so that all cells are square ? Thanks KK Here are a few of the results from a Google search with "Excel square cells" http://excel-tips.blogspot.com/2004/12/square-cells-in-excel.html http://www.mrexcel.com/tip071.shtml http://answers.yahoo.com/question/index?qid=20080711081750AAwvQnw A Google with 'free graph paper' also gets some useful results best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "KRK" <...

Help with LOOKUP function
This function is in a workbook with 2 sheets. It _almost_ works perfectly. These "C" columns in two different sheets '2005-2006'!C:C,'2004-2005'!C:C, contain names of people. The D column in one of the sheets - '2004-2005'!D:D - contains a date associated with the person's name from the C column of 2004-2005 sheet. This formula is in the "D" column of Sheet 2005-2006. =LOOKUP('2005-2006'!C:C,'2004-2005'!C:C,'2004-2005'!D:D) The concept is for the formula to lookup the value (person's name) in column C of 2005-2006 a...

Sum a column that meets two criteria
I need to sum a column of numbers if it matches two different criteria. I can set up the SUMIF easily for meeting one criteria, but I need to also sum the column if it meets that criteria, and another. For example: A B C 1 150 ABC MS1 2 200 DEF MS0 3 100 LMN MS0 4 125 ABC MS1 5 175 LMN MS1 6 225 DEF MS0 I need to have a formula that would say <<Sum column A IF column B = "DEF" AND column C = "MS0">>. (and so forth for the other combinations). I know there has to be a way to do this, probably using a combination of an IF and SUMIF functions - but i keep...

A Lookup function does not work
Hi, This is my first posting. I am using Exel 2000. I have 2 separate spreadsheets that have some similar columns but not all of the data in the similar columns is the same. What I want to do is take column A in spreadsheet#1 and find this same value in Column B in Spreadsheet#2 and then insert into column 3 in spreadsheet #1 a value from a different column in spreasheet #2 that corresponds to the row in which the value was looked up in Column B in spreadsheet#2. What I am doing is comparing 2 different inventory files that have stock codes in columns and quantities in another column, but n...

return cell reference in a table based upon given lookup criteria
Is there a way to return the cell reference, or column/row coordinates, of a cell within an array or table by providing lookup criteria? Perhaps something like this: For a table of value in A1:E10 F1: (the value to find) G1: =ADDRESS(MAX((A1:E10=F1)*ROW(A1:E10)),MAX((A1:E10=F1)*COLUMN(A1:E10))) Note: Commit that array formula by holding down the [Ctrl][Shift] keys and press [Enter]. That formula returns the address of the 1st cell containing the value in F1, or #VALUE! if there is no match. Am I on the right track here? *********** Regards, Ron XL2002, WinXP-Pro "Travis" ...

Summing only values in visible columns, not in hidden columns
I need to write a SUM function that will add up several cells in a row (for example, A4:S4) - but I only want it to add up the values in those columns that are visible, not the values in those columns that are hidden. Is there any way to add only values in the visible columns and not the hidden ones? Thanks. Hi see your other post -- Regards Frank Kabel Frankfurt, Germany "BW" <BW@discussions.microsoft.com> schrieb im Newsbeitrag news:9F0C8336-F3FA-4E0F-9410-BD7A9E544D5B@microsoft.com... > I need to write a SUM function that will add up several cells in a row (for &...

Advanced Lookups
Is there any way to make an advanced lookup the default lookup? so you don't have to always choose that option when doing a lookup? Thanks for any help. Tracey D Advanced lookups ARE the default unless you've done something to make it now so. There isn't any way to "choose" the option when doing a lookup that I know of unless you have some type of customization (easy to do) that would give the user an option. patrick dev support -- This posting is provided "AS IS" with no warranties, and confers no rights. "Tracey D" <...

How to make a single column grow?
How do I make a single column, for grading, that grows from zero to one hundred as the student enters the grade they achieved on each assignment? -- Joe ...

No column headings after customisation
Hello all, We're currently rolling out Microsoft CRM internally. We performed all the customisations on a test machine, and then imported them onto the live server. However, we've now noticed that all the column headings are blank on the live server. For instance, if you view a list of accounts, instead of having Name, City etc in the headings, it says nothing at all. It's also not possible to sort the columns by clicking on them, so the underlying schema must be pretty messed up. Has anyone come across a problem like this before? Emma Burrows Emma, can you see atleast the fi...

Column Size
I hope this is possible to do, if it is I don't know how to do it. have text at top of column A and I have more below. So it all lines u correctly, I need the top of column A to be wider than other parts. I this is possible to do, please let me know.Also If there is anothe option, please let me know. Thanks in advance -- jmurrrd ----------------------------------------------------------------------- jmurrrda's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3192 View this thread: http://www.excelforum.com/showthread.php?threadid=51651 jmurrrda, What I'...

Fetch XML to Query Expression
I'm trying to convert the following Fetch XML statement to a Query Expression to post using the RetrieveMultiple webservice: <fetch mapping="logical"> <entity name="role"> <attribute name="name" /> <link-entity name="systemuserroles" from="roleid" to="roleid"> <filter> <condition attribute="systemuserid" operator="eq-userid" /> </filter> </link-entity> </entity> </fetch> I haven't been able to find an documenta...

How does one print the columns from a Visio ERD diagram?
I see them but can't print them. ...

Splitting columns
Hi, I am stuck for an answer here, so any help is appreciated. What I'm trying to do is take two columns, sort them and then break those two columns into four columns to fill the whole page while still maintaining alphabetical order. Thanks See if this link helps: http://www.mvps.org/dmcritchie/excel/snakecol.htm -- HTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== "BBB" <bblatz@bigeagle.ca> wrote in message news:1155221940.396073....

Controlling hyphenated words in columns.
Is there a way to set up linked columns so that words don't hyphenate? I have tried spacing, reducing font by a half point and adjusting the size of the column but it seems like a lot of work to set it up, then when printing sometimes the layout changes again. Tools, language, hyphenation. Or if you want the hyphenation turned off for this and all future publications, tools, options, edit tab. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "misskitty" <misskitty@discussions.microsoft.com> wrote in message ne...

how do i move columns on one sheet to columns on another sheet
I have sheet one which is column "A" student's name columns "B thru K" are a point system in each column (by 3 points either a score of 1, 2 or 3). Column "L" is the total points summed across "B" thru "K". (a total of no more than 30). Sheet 2 is 5 columns with each column being worth 6 points. Column "A" is 25 thru 30 points, column "B" is 19 thru 24 points, column "C" is 13 thru 18 points, column "D" is 7 thru 12 points and column "E" is 0 thru 6 ponts. I need column ...

Calling employee lookup from button through VBA code
Dear All, Can anyone show me how to call an existing GP employee lookup from a button of a modified form through VBA code. Thanks in advance. -- Developer Hi, If I'm understanding the question - you need to add the lookup button to your project and make sure your project provides that it runs on the modified form. Leslie "Dexdev" wrote: > Dear All, > > Can anyone show me how to call an existing GP employee lookup from a button > of a modified form through VBA code. > > > Thanks in advance. > > -- > Developer Hello Dexdev As per...

Limiting a column to certain values
I have a table that contains PLSS information and want to restrict the columns to certain values. Since there is a pattern in what they are restricted to, I wonder if there would be an easier way than to create a lookup table and use a constraint. For instance, my values for one field is limited to 3 characters: from 01-49, with the third character always an 'E' or 'W' Would this be easier done in a query or stored procedure or function than to create a lookup table? Thanks for your help. In the case you describe you can use a CHECK constraint: CR...