Combine 2 rows if name is same in Column B & C on both

Combine 2 rows if name is same in Column B & C on both

2 spreadsheets - Sheet 1 is bigger with extra names in column B & C
Lastname Firstname

Both - Column B & C Lastname Firstname - both sheets

Sheet 1 has data in Col. D & E
Sheet 2 has data in Col. F & G

Sheet 1 has extra names not in Sheet 2

If Sheet 1 B&C = Sheet 2 B&C ,
 then add F&G columns from sheet 2 ,
 behind D& E columns on sheet 1 , for the match of names in Column B &
C

Thanks
kerns.walter@epa.gov

0
11/13/2007 3:50:53 AM
excel 39879 articles. 2 followers. Follow

1 Replies
471 Views

Similar Articles

[PageSpeed] 13

On Nov 13, 12:50 pm, wk <kerns.wal...@epa.gov> wrote:
> Combine 2 rows if name is same in Column B & C on both
>
> 2 spreadsheets - Sheet 1 is bigger with extra names in column B & C
> Lastname Firstname
>
> Both - Column B & C Lastname Firstname - both sheets
>
> Sheet 1 has data in Col. D & E
> Sheet 2 has data in Col. F & G
>
> Sheet 1 has extra names not in Sheet 2
>
> If Sheet 1 B&C = Sheet 2 B&C ,
>  then add F&G columns from sheet 2 ,
>  behind D& E columns on sheet 1 , for the match of names in Column B &
> C
>
> Thanks
> kerns.wal...@epa.gov

If it is possible to enter a new column after column B in your sheet 2
then you
could enter following formula to your NEW sheet2!column C:
=A1 & " " & B1
and copy it down.

then in your sheet1!column F you could use this formula:
=vlookup(A1 & " " & B1,sheet2!C:H,5,false)
and in your sheet1!column G:
=vlookup(A1 & " " & B1,sheet2!C:H,6,false)

maybe you need to change the numbers for Col_Index, because
I have no Idea how your sheet is built, but otherwise it should work.

hth
Carlo

0
11/13/2007 4:25:58 AM
Reply:

Similar Artilces:

ING Direct #2
I am trying to set up an ING Direct account in Ms Money 2003 following the directions on the ING web site. After completing step 12 which is "make sure ING DIRECT is selected" in the Online setup window, click next. When I do this it prompts me for a username and password. This is not what the instructions say should happen. They say Money should connect to the internet and download information for ING DIRECT. If I try to enter my customer # and PIN as the user name and password it tells me the password must not contain any numeric characters. Upon close inspection, under the passwor...

Managed User #2
Testing to see if this setup allows me to be properly identified as a managed user. ...

Problems inserting pictures in Excel 2002 sp-2
When inserting a picture into a work sheet, nothing shows up but the outline/edit box. If you grab the box and move it on the sheet, the picture will appear. If you scroll up or down, the portion of the picture that goes off screen will not show up when you scroll back to the center of the screen. Grab and move it again and it shows up again.... HELP?????? My IT guy is lost on this one! It sounds more like a windows display problem to me. Do you have the zoom factor set to 100%? (It may help.) But maybe you could try closing other applications or changing the display settings. I use...

Historic currency exchange rates for Schedule C reporting
I'm trying to get a Schedule C report out of Money but cannot correct the exchange rate it is using. I have an account with entries in GBP for 2008, but that are converted to USD for my Schedule C report. However, pretty sure the exchange used is supposed to be either some annual average as mandated by the government or the daily rate at the time. Money is just using today's rate, which is obviously causing inaccurate and varying results each time I run the report. Is there any way to tell money to use the rate on the date of the transaction, or give it the rate to use for a r...

Find value in a column and insert rows above
The set up looks like this: ColU ColV ColW ColX Y N N N Y N N N N Y N N N N Y N N N Y N N N Y N N N Y N N N Y Y Columns will always be U through X and will always be sorted in this order. I need to find the first Y in each column and insert 2 rows above that row. On the blank row above the first Y, I need to highlight in yellow and put title in the first cell, such as New, Old, Existing, Deleted. Any help would be greatly appreciated. Thanks for your time, Dee If desired, send your file to my address below. I will only look if: 1. You send a copy of this ...

need help with formula #2
how to write a formula? if cell =>24 multiple by 22. if cell is <24, multiply 24 by 22, multiply difference by 33 add together -- Kerri Where do I find the difference "Kerri Olsen" wrote: > how to write a formula? > if cell =>24 multiple by 22. if cell is <24, multiply 24 by 22, multiply > difference by 33 add together > -- > Kerri See Help for "IF worksheet function" I can't be more specific without a better description of the alternative calculations. Jerry "Kerri Olsen" wrote: > how to write a formula? > if cell ...

2 pivot tables on one tab
Is it possible to put 2 Pivot tables on the same tab in my workbook? Or does anyone have any ideas on what would be the best way to chart Capital Pircahses? I have 5 coums and 4 rows. The rows are per division, and the columns are for the disposal amount, gain or loss, book value, depreciation & purchase price. I already have one pivot table in my report. Would this be too repeatative? Thanks You can put multiple pivot tables onto a worksheet. If the second one is based on the same data, select and copy the first, select a cell some distance away, and paste it. Each pivot table...

What version of Excel converts Lotus 1-2-3 spreadsheets?
I had a 30-day trial version of Microsoft Office (which I do not know what version it was) and purchased the Student 2003 Office which no longer converts Lotus 1-2-3 spreadsheets to the Excel spreadsheet. I'd like to upgrade and do not know which one to purchase. Roberta I don't know if the S&T sku for Excel limits the files to open, but in XL2003 (part of Office 2003 Pro) there is a Lotus 1-2-3 option under File>Open...>Files of type drop-down>Lotus 1-2-3 -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS www.ni...

Counting rows of blanks across certain columns
I have a survey whose answers were recorded in Excel. The answers for a particular question extend from Q6 to Z505. I need to count the people who did not answer the question (that is, the people, entered in rows 6 to 505 that left columns Q through Z blank). I'm not sure how to do this. Can someone offer a suggestion? Hi You can use a formula like this in Column AA =IF(COUNTA(Q6:Z6)=0,1,"") copy down till AA505 You can use a simple Sum formula now to count the empty ones =SUM(AA6:AA505) -- Regards Ron de Bruin http://www.rondebruin.nl "crossingboston" <...

Click on link problems #2
When I click on a url link in email---a box opens saying "Open Link Browser". And also a browser window. How do I get rid of the smaller link browser? Read your reply on your post of the 17th? -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What do the Outlook Icons Mean? -Create an Office 2003 CD slipstreamed with Service Pack 1 ----- "Robin" <robinhood2@juno.com> wrote in message news:atmnk0pgs4m272iunkegt0rvq4ssjqisig@4ax.com... > When I click on a url link in email---a box opens saying "Open Link > Browser". A...

Big problem! DNS name Space.
I have a need to share one DNS name space between four AD Exchange forest ( I know what your thinking, why would you do this! All I can say it’s government). All the forests are running Windows 2000 with Exchange 2003. My problem is I can’t seem to find a way to allow this to happen. I know it’s easy to accomplish this if it was just two forests, but how do you share the same E-mail address across four AD forest? Basically I need mail to come in into the first Exch Org and if the recipient is not found on that Exch Org to continue down the line to the next Org, in till the user is foun...

Grouping columns
I have a spreadsheet where the columns of data are in logical groups. For example, I have 5 columns of data for the month of February, followed by the same 5 columns but for March etc. What I want to do is than when the user scrolls right or left the whole of one month moves and not part of it. is there some way to group the columns so that they will scroll as a unit? Jonathan Blitz AnyKey Limited Israel You can use one unused row, like row 1, and merge the 5 cells across for each month..........then use that row to do your scrolling....... Vaya con Dios, Chuck, CABGx3 "Jonatha...

HIDDEN COLUMNS #3
SOMEHOW I'VE HIDDEN COLUMNS A AND B, AND NOW I CAN'T UNHIDE THEM. HELP! Click on the header for column C, highlighting the entire column. Without releasing the mouse, drag until you're over the column of row headers (on my version of Excel, in Office 2003, I see the tool tip change to '3C'). Now you should be able to Format > Column > Unhide. "acp20770" wrote: > SOMEHOW I'VE HIDDEN COLUMNS A AND B, AND NOW I CAN'T UNHIDE THEM. HELP! If you select the sheet then go to window and unhide it will restore the Hidden windows. "acp20770&q...

Socket relared problem (getting free port number dynamically) #2
Hi all, i have one problem related to thread. i am having my main thread running which will on some event create a thread for validation purpose and then will start working. and now this validator thread will create a socket and it will bind to port 20111. and then will wait for client to connect. but before waiting for client to connect it will send a message to the main thread that validation is done. but in this way i need to stick with port 20111.which i really don't want. Is it possible for me to get a free port from operating system or from winsock library and then i should bind t...

graphic deleting #2
I have made myself a simple macro that copy a cell and paste it 50 cells below. It work fine with text and numbers but I have tried it with graphic recently and strangly enough it is adding a other graphic the same as the first one each time it copies. I have discover that because when I tried to correct my graphic and want to erase the others there was one more graphic to erase each cell down below, very long process and I would like to know why it is doing that and if there is a command to erase all the selected cells contents all at once like the text contents. Thank you very much Show th...

Adding and subtracting from a column.
I have a stock list with a "quantity" column. What I would like to do is add the new stock to the existing quantity column or subtract requisitioned items from the quantity column. I guess it is a "self totaling" column I need to make. As a separate exercise I would like to make a new column for orders. Insert a quantity on that column for any of the items ( rows) I want to order and then print it out... BUT I only want to print the rows that have quantities in that column ( i.e. the actual items I am ordering ). This column is not linked to the above column. Could an...

Default Public View has records but columns are blank
A custom entity has a default list view but the entries in the list do not show any values. There are a number of lines in the list with an icon only. If I try to delete one the message says select an entry before attempting operation. I cannot select the lijne either. Have created many views before and never seen this. Other views are ok? Hi Steve, Try publishing the entity and see if this resolves the problem. HTH, Niths "Steve" wrote: > A custom entity has a default list view but the entries in the list do not > show any values. There are a number of lines in the...

How to combine text from 3 cells into 1 cell with space and ", bet
How to combine text from 3 cells into 1 cell with space and ", between. Is it a formala I can use? HI Try this =A1&" "&""""&B1&" "&""""&C1, adjust range to your needs HTH John "JOF" <JOF@discussions.microsoft.com> wrote in message news:3BD2A42E-BCB9-4504-8454-727E366E25DD@microsoft.com... > How to combine text from 3 cells into 1 cell with space and ", between. Is > it > a formala I can use? ...

Sheet you want to copy contains the name...
When I copy a worksheet I get the repeated message box that the destination workbook already contains the name "a", "aa", "prntoutline", "ssd", "q" and "zz". I have to select yes to each message box to allow the worksheet copy to complete itself. I do not have these names within the worksheet (chekced by Insert>Name>Define). How can I delete these nsmaes that do not seem to exist? Thanks. Names can be hidden, so Insert=>Name=>Define may not show them. In the troublesome workbook make a blank sheet active, then go...

Counting how many different names in a long list
I have a list of 332 names many of them are duplicated. I am looking for a formula or function that can count how many unique names there are in the list. Can anyone help? -- Quaisne ------------------------------------------------------------------------ Quaisne's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28052 View this thread: http://www.excelforum.com/showthread.php?threadid=501357 This'll count the number of distinct values in a range: =SUMPRODUCT((A1:A99<>"")/COUNTIF(A1:A99,A1:A99&"")) (adjust the range to matc...

Link Rows Between Worksheets
Hi- I am trying to link rows (that are infinitely long in terms of data) between two different worksheets so that I dont have to retype all of the data. (I want them to be active links, so that if the data changes, it will automatically update). Is there a way to do this? (The first worksheet is a master that has a whole lot of information, and I would like the other worksheets to reference rows from the original...) Thank you very very much.. In the other sheets you will have to set then up eg: in sheet2 select the cell you want then type an equals sign = got to master sheet and se...

Pressing Enter to skip rows
We have a pro-forma in Excel for clients. Anyway, the cells where the enter information from a drop-down list are three rows apart, goin down the page. Is there anyway for the client to press Enter just once and go thre rows down rather than pressing enter three times? I am aware that if the sheet is locked with the particular input cell unlocked then Tab will take you to the next box however, we need Ente to do the same. I imagine a macro could achieve this. Thanks in advance D -- DoctorWar ----------------------------------------------------------------------- DoctorWard's Profile...

Inserting rows into sheet that contains a formula
I have a typical financial sheet where the columns sum (sum(a5:a15)) and the rows sum (sum(a5:h5)). The summation columns are locked and the sheet protected. Is there a technique whereby I can have the user insert a row which will keep the formats and row sum as well as changing the formula in the column sums? Thanks. Rick Hi Rick, The following method is not foolproof but generally it works OK. Insert a blank row between the bottom of data to be summed and the row with the sum formulas. Include the blank row in the sum formula. Protect the blank row along with the formulas and what...

How to succesfully sort column of formulas?
I am trying to sort a column of formulas that consist of cel references. Example: Column: Cell 1 holds: =D10 Cell 2 holds: =D11 Cell 3 holds: =D12 Data: D10=1 D11=2 D12=3 When I try to sort the column of formulas, the formulas change cel references to other cells and mess up the data. How can I sort a column of cells of which all of them contain formula and have the cells keep the references to the right place -- Message posted from http://www.ExcelForum.com Hi- If you write your formulae with absolute references rather tha relative references, you can sort them. Formulae should look ...

Get External Data
I have a 2 column table which is likely to change length whenever the Refresh Data menu option is selected. The table links to another software package (SAGE). I don't have formulae in adjacent columns so my problem can not be solved by checking the "Fill Down formulas in columns adjacent to data" box. I do, however, have text in adjacent columns (which the user selects from an in-cell drop down list) which I need to remain aligned with the relevant row. The "insert entire rows for new data ..." option does not appear to do what it says! It only inserts rows in the...