#### "intersect" 2 slightly-different worksheets

```Hello, All!
I think this 'problem' requires a pretty simple solution; I just don't
know what!
Say you've got 2 worksheets, one w/ 50 rows and 6 columns, all
relating to employee data (e.g., col headings like 'employee name',
'date of hire', 'hair color', and 3 other cols), and then a much longer
sheet, with 500 rows, and 7 columns relating to employee data--6 of
those cols being the same headings as the much-smaller sheet, but in
addition the larger "master" sheet also has a column called "employee
I.D. number", containing unique number-values.
Also: All the employees on small sheet are included w/in the large
one....
What I'm trying to do is: "Intersect" the employees' data from the
small sheet with the same records from the large sheet and include that
7th column w/ I.D. #'s, and form a 3rd sheet from that data. In other
words, the new/created worksheet would be identical to the original
small sheet, with the addition of the one additional column w/ the
correct/corresponding I.D. number.
Would I use some kind of filter to do this? or worksheet consolidation?
Thanks much in advance for your time & attention to detail [:-)
terry b.

```
 0
1/15/2005 10:34:39 AM
excel 39879 articles. 2 followers.

1 Replies
515 Views

Similar Articles

[PageSpeed] 38

```Terry

If the data in the six columns on each sheet is absolutely identical, you
could set up a 'key' on the large sheet. (Insert a column at A and enter in
A2 say =B2&C2&D2&E2&F2&G2&H2 and copy down. This will give you something to
look up on that provides uniqueness). Then in column G on the third
worksheet (in G2) enter

=VLOOKUP(A2&B2&C2&D2&E2&F2,LargeSheetRange!\$A\$1:\$H\$500,8,FALSE)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS

"terry b" <riprap1951@msn.com> wrote in message
> Hello, All!
> I think this 'problem' requires a pretty simple solution; I just don't
> know what!
> Say you've got 2 worksheets, one w/ 50 rows and 6 columns, all
> relating to employee data (e.g., col headings like 'employee name',
> 'date of hire', 'hair color', and 3 other cols), and then a much longer
> sheet, with 500 rows, and 7 columns relating to employee data--6 of
> those cols being the same headings as the much-smaller sheet, but in
> addition the larger "master" sheet also has a column called "employee
> I.D. number", containing unique number-values.
> Also: All the employees on small sheet are included w/in the large
> one....
> What I'm trying to do is: "Intersect" the employees' data from the
> small sheet with the same records from the large sheet and include that
> 7th column w/ I.D. #'s, and form a 3rd sheet from that data. In other
> words, the new/created worksheet would be identical to the original
> small sheet, with the addition of the one additional column w/ the
> correct/corresponding I.D. number.
> Would I use some kind of filter to do this? or worksheet consolidation?
> Thanks much in advance for your time & attention to detail [:-)
> terry b.
>

```
 0
1/15/2005 11:03:41 AM

Similar Artilces:

intersection operator across workbooks?
I've got regions columns and rows named by their top-most or left-most name. I.e. "Site1Total" for a row, and "MarchCost" for a column. So, when I do a cell "=Site1Total MarchCost" it shows me the total cost for Site 1 in march. Is there a way to reference those intersections across workbooks? thanks, -bld The intersection operator is just like any other operator. You can use it across WBs, BUT ... you'll need to tell XL where to find the WB. This means including the full path together with the named ranges. It's easiest to have both WBs open, ...

line chart problem: data points intersecting with border
The first & last data points of my series are intersecting with the vertical border of the chart area. I'd like to have a bit of a gap (so I don't always have to reposition the data label for the final value so that it falls inside the border... plus I think it would look nicer), but none of the "offset" or any other controls in the chart options seems to have any effect. Any ideas? Thanks, DG Hi gerridge, Check the X axis Scale setting "Value (Y) axis crosses between categories" is checked. Right click X axis and pick format Axis to display Format dialog. g...

Union and Intersect range... but any SUBTRACT?
Hi all, I see the functions to union and intersect a range, but I don't see a function to subtract one range from another. Does anyone know if this function exists, or if there is a VBA function out there to do this I can paste into my application? Thanks! Ray Hi Ray, Found in a search of newsgroup archives http://groups.google.com/advanced_group_search?q=group:*Excel*&num=100 search all words: union intersect subtract Tom Ogilvy posted a SubtractRange subroutine in a reply 2000-08-06 that should work for you. http://google.com/groups?threadm=%23OOGqH7%24%24...

Marking point of intersection on double y axis chart?
I have two sets of data plotted on a 2 axis line chart. The data resembles a cross with one set increasing and the other decreasing. Is there a simple command that will draw on marker lines showing where they intersect, with their respective values on the axes? -- TheBigLoofah ------------------------------------------------------------------------ TheBigLoofah's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31797 View this thread: http://www.excelforum.com/showthread.php?threadid=515232 Why don't you have your workbook do the calculation of intersection...

how can i finde the cordination of intersection betwin 2 chart?
how can i finde the cordination of intersection betwin 2 chart? ...

Indicate intersection point on a two-line graph
Hi, I have a simple line graph with two lines (line A and line B). They intersect at a given point (the break even point). How do I indicate on the graph that this is the break even point. I know its very easy to do with an arrow and text box. However, I would like the arrow and text box to move to a new intersection as I change the numbers around. Thanks, Greg Andy Pope has an example on his site which should help: http://www.andypope.info/charts/intersection.htm -- John Mansfield http://cellmatrix.net "Greg" wrote: > Hi, > > I have a simple line graph with...

Target cell by "offset" of named intersection?
How do I refer to a cell by using an offset reference to another? My current targets are referred to by intersection off named columns and rows - eg: =monthtotal_JUL stock_bought gives me intersection of row "monthtotal" and column "stock_bought", but now I want another reference to cells above the "monthtotal" row (still in the stock_bought column) without having to name the rows separately. I played with the R1C1 convention ( R[-5] etc) but I'm not sure it's compatible with the intersecting name reference, or if it is, just where to put the offset w...

Intersections
Hi, Is there any way in which I could click in any cell on a workbook and the column would highlight up to that cell, and the row would also highlight up to that cell? It's something that might make data entry a lot easier when viewing a couple of documents at the same time! Dan. -- Voodoodan ------------------------------------------------------------------------ Voodoodan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=597 View this thread: http://www.excelforum.com/showthread.php?threadid=395322 You could try something like this, Dan: http://www.cp...

Can you intersect two objects and then draw a line around both?
I put two objects overlapping each other, and then I wanted to draw a line around both, but not each individually...can I do this in Publisher? I am using Publisher 2003 with Service Pack 1 Before I say "Sure you can", what kind of objects? What kind of line? -- JoAnn Paules MVP Microsoft [Publisher] "kjhearne" <kjhearne@discussions.microsoft.com> wrote in message news:135B590F-6628-4257-8557-F592E9EE870B@microsoft.com... >I put two objects overlapping each other, and then I wanted to draw a line > around both, but not each individually...can I do thi...

HOW TO: Intersect two ranges?
Hi TWIMC, I've the following code at the start of the 'Worksheet_SelectionChange' private sub procedure but for some unknown reason to me it doesn't think my selected range is within rows 1 to 10. Basically, when I select a cell range, if some part of that selected range crosses over row 10 then I don't want the procedure to run. So if my Target range is \$B\$7 or \$B\$5:\$B\$10 I want it to Exit Sub If Intersect(Target, Application.ActiveSheet.Rows("1:10")) Then Exit Sub Can someone enlighten me as to where I am going wrong TIA KM One way: If Not I...

Get the Intersection range
Hi, I have two excel range objects. I want to know whether these are intersected or not. If they are intersected then i need the intersected range or the starting cell and ending cell of intersected range. Can any one help me? Cheers, Srikanth Hi Dim rng1 As Range Dim rng2 As Range Dim inters As Range Set inters = Application.Intersect(rng1, rng2) If Not inters Is Nothing Then 'code Else 'other code End If -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "Venkat" wrote: > Hi, > > I have t...

Adding an intersecting line on a chart
Hi All Sorry if this is simple but so am I ! I have a basic X-Y line chart with about 6 values plotted to form a wiggly line. I've added a trendline to this to smooth it. I now wish to add a vertical line at a certain value on the x axis, draw this up to the trendline, then trace a horizontal line across to the Y axis. It's the sort of thing you'd do in secondary school but can I do it in excel ?!?!? ...no Any help would be appreciated. Regards John P http://www.tushar-mehta.com/excel/charts/straight_lines/index.html Jerry John Phillips wrote: > Hi All > > So...

Intersection of two lines #2
Dear friends: I am having EXCEL2003. Given two sets of points (2 points per set), I am able to draw two lines. Unfortunately, the two lines do not intersect. How can I EXTEND these lines so that I can read (or return by XL) the intersection point off the chart? Thanks. Lee CC You could chart the two data series with only makers (no lines) then add trendlines to each data series to see where they cross. But it is easier to do it with math. For each pair of data points, use SLOPE and INTERCEPT to find the slope and intercept of the lines that passes thru them Let these be m1,b1 and m...

Determine Intersection of 2 lines using Excel VBA
Y values - I have y-values in 2 rows of my spreadsheet, say A5:W5 and A10:W10. X values - The x-values are common to both rows, and listed in A2:W2. I would like to use vba to determine the intersection of these y- values, and determine where on the y-axis and x-axis do the lines intersect. I would like for the intercept to show up in a message box. Could someone please assist with some vba code on this "intercept analysis" Thanks in advance. V ...

Xcel or Access sort columns of names so that I remove the intersect between 2 columns from one of the columns?
Hi, I have currently in Xcel and long list of X English phrases in one column with associated numerical data about each name in adjoining columns. I have on another workbook (which could of course be pasted into this workbook)another similarly set out column of Y English phrases again with ajoining columns having numberical data row that the phrase. I would like to be able to sort the second column of phrases removing all the phrases that occured in the first column. Ideally it would be great to have the numerical data for the remaining English phrases still on the same rows (not necessary ...

intersection of 2 graphs
I plotted 2 sets of data on the same set of axes.. now I need to find the intersection point of the two trendlines. Please let me know how I can do this (if I can)? This is probably very trivial.. really appreciate all the help. Thanks! Hi, Have a look at this explanation. http://www.andypope.info/charts/intersection.htm Cheers Andy utopian_sorceror wrote: > I plotted 2 sets of data on the same set of axes.. now I need to find the > intersection point of the two trendlines. Please let me know how I can do > this (if I can)? > > This is probably very trivial.. really a...

how do i get the intersection of two arrays in excel?
The intersection operator is the space character So "Array1 Array2" will give you the intersection, or #NULL if there isn't any -- Kind regards, Niek Otten "Bibhu" <Bibhu@discussions.microsoft.com> wrote in message news:05614937-07BB-4941-A947-804390CD32FF@microsoft.com... > ...

Output intersection of table
Hi, I have TableA of data which needs checking row by row. TableB on a different sheet contains the values which need to be output. In TableA need to check Col A and then the value in Col C and return the intersecting value from TableB into Col D (in TableA). e.g TableA: ColA ColB ColC ColD Blue Man Car Red Auto Truck TableB: Red Blue Black Car 9 5 2 Truck 7 4 3 Therefore: In Row1 in TableA the value in ColD needs to be 5. In Row2 in TableA the value in ColD needs to be 7. thanks in advance, Hamish --- Message posted from http:...

Finding intersection point in rows and columns
I am trying to work a macro which will do the work for me (go figure). Here is the easy example. I have a worksheet which has a 3 columns an a bunch of rows: the column headings are " ", "total", "add-on" the rows then consist of "mike","1,000","200"... then next ro "cindy","1,230","23" etc I would like a different worksheet to have a macro button that ca take the data and put it into a new table and stuff. I was thinking along the lines of creating labels on the top and left and then referencing the ...

Intersection point in graphs
All, I have 2 sets of data. One set is directly proportional to the data on x-axis and the other is is inversely proportional. I want to find out at which point on the x-axis do the 2 data sets intersect. I can see it graphically but I need some kind of a formula to spit out that intersection point. Any help, suggestions will be appreciated. Thanks, R.K. General answer: If we have two functions y1=f(x) and y2=g(x), we find the point(s) of intersection by setting y1=y2 [f(x)=g(x)]and solving for x. Specific case: directly proportional function y1=f(x)=ax indirectly proportional funcito...

Sum of the intersect of two named ranges
I have an excel worksheet with multiple named ranges - one that is horizontal and another that is vertical Range1 = A1:Z5 Range2= C1:C60 I want to sum the intersect of these two - i.e cells C1:C5 I tried sum(Range1 Range2) and got something else. How can I sum the intersect of two ranges. TIA shikarishambu was thinking very hard : > I have an excel worksheet with multiple named ranges - one that is > horizontal and another that is vertical > > Range1 = A1:Z5 > Range2= C1:C60 > > I want to sum the intersect of these two - i.e cells C1:C5 > > I tried sum(Range1 ...

Add vertical line at intersection of 2 curves
Hi folks, Picture, if you will, an Excel graph with 2 bell-curves plotted on it...one curve shows the freqency distribution of measurement values at "good," or reference, sites, and the other shows the distribution of values at "bad," or impaired, sites. At some point on the graph the curves intersect (hopefully at the inflection points, assuming a more-or-less normal distribution for both populations of sites). The "curves" are actually XY scatterplots with smoothed lines and no markers, using data sorted into uniform "bins" generated by Tools...

One range inside another, Intersect
After I had the same situation, and reading some posts about checking to see if the cells in one range were contained in another range, I wrote the function below. It returns the boolean True or False value, and optionally, if range A is not entirely within range B, the first failing cell is returned. Public Function bRngAinRngBF(RngA As Range, RngB As Range, _ Optional FailedRng As Range = Nothing) As Boolean 'True if every cell in RngA is in RngB. > 1 Area is OK in either. 'When False, the 1st cell in RngA not found in RngB is returned in FailedRng. Dim Ce...

Intersect of 2 strings
Happy New Year y'all, It's my day to be stumped. I'm trying to extract an Intersect between two strings where the letters stay in the same order as the original strings. For example: string 1) "Happy new year" and string 2) "new year rocks!" The result I want is: " new year " IEnumerable provides the correct characters, but the results are not in order of the original strings. CompareOrdinal (probably the wrong use) works where I can get the start and end of each string, except when the strings begin or end has the same position...

Intersection Referene Operator
I've just been surprised to come across reference to the <space> intersection reference operator. Surprised because I've used Excel for years and consider myself reasonably advanced but have never come across this before, i.e SUM(A1:A6 A5:A10). I've been trying to think of a useful application for this functionality though and haven't thought of anything. Can anyone tell me of a time when they've found this of use, or any scenario where it could be useful. Many thanks.....Jason Think of a table: column headers Jan , Feb, Mar, etc,; row headers North, South,...