two lists

I have two lists in separate worksheets. The data is almost identical.
There is a column for ID and a column for a post code. I need to be
able to put all the data into one sheet with three columns. One will
list the the IDs and the others will put the two postcodes next to
each other. Unfortunately, some IDs only appear in one sheet, some in
the other and some are in both. So I will have some records complete
and some partial complete.

I have tried to use a look-up but I have confused myself. help!

0
2/1/2007 4:07:50 PM
excel 39880 articles. 2 followers. Follow

2 Replies
338 Views

Similar Articles

[PageSpeed] 15

bazblazer,

I'm not sure I've actually heard a question.  I've made an example.  If 
you're not using a fixed-width font like Courier, this may not line up well 
in your news reader.

Sheet 1
ID  Post Code
A     1
B     1
C     2

Sheet 2
ID  Post Code
A     1
B     2
D     4

Is below the output you want:
ID  Post Code1  Post Code 2
A     1          1
B     1          2
C     2
D                4

If so, try Data - Consolidate.  Check "Use labels in" both "Top row" and 
"Left column."  If not, change the example so it shows all possible 
circumstances, and the output you want.
-- 
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"bazblazer" <whypenalties@googlemail.com> wrote in message 
news:1170346070.749905.23160@p10g2000cwp.googlegroups.com...
>I have two lists in separate worksheets. The data is almost identical.
> There is a column for ID and a column for a post code. I need to be
> able to put all the data into one sheet with three columns. One will
> list the the IDs and the others will put the two postcodes next to
> each other. Unfortunately, some IDs only appear in one sheet, some in
> the other and some are in both. So I will have some records complete
> and some partial complete.
>
> I have tried to use a look-up but I have confused myself. help!
> 


0
someone798 (945)
2/1/2007 5:33:23 PM
Another option...

I'd add a new sheet.

Copy the values in column A of sheet1 to A1 of this new sheet.
Then copy the values in column A of sheet2 under the last entry of that new
sheet (in column A).

Then add a header in A1 (insert a new row if you need to).

Then use Data|Filter|Advanced filter to get the unique entries and plop those
into B1.

Debra Dalgleish shows how:
http://www.contextures.com/xladvfilter01.html#FilterUR

Then use =vlookup()'s to return each of those amounts in column B and C.

In B2:
=if(iserror(vlookup(a2,sheet1!a:b,2,false)),"",vlookup(a2,sheet1!a:b,2,false))

In c2:
=if(iserror(vlookup(a2,sheet2!a:b,2,false)),"",vlookup(a2,sheet2!a:b,2,false))

Debra Dalgleish has some notes:
http://www.contextures.com/xlFunctions02.html  (for =vlookup())

bazblazer wrote:
> 
> I have two lists in separate worksheets. The data is almost identical.
> There is a column for ID and a column for a post code. I need to be
> able to put all the data into one sheet with three columns. One will
> list the the IDs and the others will put the two postcodes next to
> each other. Unfortunately, some IDs only appear in one sheet, some in
> the other and some are in both. So I will have some records complete
> and some partial complete.
> 
> I have tried to use a look-up but I have confused myself. help!

-- 

Dave Peterson
0
petersod (12004)
2/1/2007 5:53:11 PM
Reply:

Similar Artilces:

synchiing two computers?
Is there a way to synch Outlook on two computers? I end up with some email on my laptop and some on my desktop - it would be nice if the two could be synched so that I could have all of my email on both computers. Take a look here, it may help: http://www.slipstick.com/outlook/sync.htm -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, Bill Andersen asked: | Is there a way to synch Outlook on two computers? | I end up with some...

limited number of adresses in personal distribution list
Hello all, we moved mailboxes from an ex2000 server to an ex2003 server. Now some users (all Outlook 2003) have problems with their distribution lists in their contacts folder. With ex2000 they had no problem. Now they can't use these lists anymore: "This distribution list has reached the maximum size for your network e-mail server". According to http://support.microsoft.com/kb/238569 there is a limit of 8 kB on the server. What changed when migrating from ex2000 to ex2003 ???? Is there any workaround ??? Regards, Peter kerkhofs ...

Compare two cells in two different files and return answer
I am attempting to compare two text cells and if they both exist, I want to bring in a "yes" answer. If they don't, bring in a "no" answer. Here's an example of text. File 1 Col A Col B BRATTA ZPB_COMMON AHERNC ZPB_COMMON GENCABH ZPB_COMMON File 2 Col A Col B BRATTA ZPB_COMMON AHERNC ZPB_COMMON GENCABH So, what I want to do is say in File 1 take cells A1 and B1 compare them to File 2 and search Col A and Col B and if there is an exact mat...

Pivot table for two different data variables
I have the following table Name type Cost overhead amy C1 23 12 bob C2 129 17 cat C2 36 45 doo C2 100 32 I owuld like to create a pivot table that looks similar to: the standard pivot table. (Please ignore type for now.) name Data Total amy Sum of overhead 200 Sum of cost 12 bob Sum of overhead 130 Sum of cost 23 cat Sum of overhead 90 Sum of cost 19 doo Sum of overhead 87 Sum of cost 34 Total Sum of overhead 507 Total Sum of cost 88 But with overall total (overhead + cost) for ...

COUNTIF (?) on two criteria
I have a spreadsheet that logs work activities and records team members' completion of project units. Column B records team member names. Column C has project unit status - 'pending', 'beta' or 'completed'. It's easy to set up a formula to count how many units are allocated to each team member - =COUNTIF(B1:B1000,"Joe"). But what I need to do is set up formulas to count how many units Joe has completed, or has pending or at beta, and this means assessing data in both columns - which COUNTIF won't do. This doesn't strike me as an especially...

List Conditions
I'm creating a timesheet form and want to be able to populate a list of job codes specifically by client. For example, in the "Client" Column you select Client X from a list. The "Job Code" Column would then produce the drop down list specifically related to Client X. If in the "Client" Column I selected Client Y, the "Job Code" Column would then produce the specific job codes related to Client Y. There may be a much simpler alternative- any help would be appreciated. I'm creating a timesheet form and want to be able to populate a list of ...

How to use an image list in CListCtrl?
Hi Group, I am using Visual C++ 6.0 to build a dialog-based app. I have a CListCtrl (report view) in my dialog that contains 5 items. I want to use an image (a bitmap of 8x12) to describe the status of these items. The status of each item can be one of the following: - red square; - blue triangle; - or nothing Can someone please help me with this one. Thank you, Geo Thank you Charles for your response, > o Create an instance of CImageList > o Add the images to it (see CImageList::Add()) > o Pass your image list to CListCtrl::SetImageList() These three steps are already ...

Smart List
Our business operates out of 2 warehouses but 1 is strictly used as an overflow warehouse that we transfer product to our active warehouse as needed. We have set up GP 9.0 with multiple bins (A & B) to identify the quantites in each warehouse and we use bin transfers to match the physical movement of product between warehouses. I am trying to set up a smartlist for my warehouse staff so they can easily create a bin (warehouse) transfer picking list. My question is how can I restrict the items in the smartlist to only those items that have a presence in both warehouses. I am hoping...

How from Word a list separated by commas convert to Excel?
In Word 2002, I have a list of email adds separated by commas followed by forenames & surnames, how may I convert this list in tabulated form to Excel... A column for the email add, a column for the forename and a column for the surname... e.g ferreira @benco.co.uk,, Ferreira, Marnie italia@web.co.uk,, inglese, ANDREA info@inter.co.uk,, Mulondo, Mashudu neu@mond.co.uk,, Urbina, Ivonne brenna@web.co.uk,, Williams, Brennan -- Many Thanks Gunjani Aural sex gives me an earie feeling Copy into Excel then Data>Text to Columns>Delimited by comma. Gord Dibben Excel M...

Two IF Statements with two answers
In cell J9 I need a formula that would do the following: If G9="Yes" then F9, If G9="No" then -f9 Note: G9 has a drop down list with Yes, No answers. If there are no other alternatives that yes and no =IF(G9="Yes",F9,-F9) otherwise =IF(G9="Yes",F9,IF(G9="No",-F9,"") Regards, Peo Sjoblom "heater" wrote: > In cell J9 I need a formula that would do the following: > > If G9="Yes" then F9, If G9="No" then -f9 > > Note: G9 has a drop down list with Yes, No answers. > =IF(G9=&qu...

select date from list
Hello, I am working on a time database for employees to enter time. I have 3 tables, one with employee names, one with all the days of the year, and one where the time info gets entered. The tables look similar to the following with the employee and date table linked by employee id and the date table and work table are linked by date id. Employee Table Date Table Work Table Employee Id Date ID Work ID Employee Name Date Project Pay Period Time Employee ID Date ID In the fi...

Currency not available in currency list
Hi. I need to record bank statements in Egyptian pounds, which is not a currency supported in the list of currencies you can select. How do I specify a currency and exchange rate that is not on the drop down list? Tools, Options, Currencies, Add New Currency. "Ann" <anonymous@discussions.microsoft.com> wrote in message news:227f01c3e0e1$40465380$a001280a@phx.gbl... > Hi. I need to record bank statements in Egyptian pounds, > which is not a currency supported in the list of > currencies you can select. How do I specify a currency and > exchange rate that is not ...

Problems with a border of a list control
I'm having trouble creating a CListCtrl on the heap (i.e. new + Create()) whose border would look like a border of a control that is embedded directly into a dialog box. To be more specific, when a list control is dropped onto a dialog box in a dialog editor, its border is black but when the dialog is displayed - either by 'Test Dialog' or running the app - the border gets 'softened' (e.g. 3d on W2K and light blue on XP). My problem is that a dynamically created list control is always displayed with a black ugly border (i.e. as seen in the a dialog editor). It l...

Two axis
Positioning the Vertical axes: I have a chart where there are two axes. The first implementation produced the two scales on the right side, one inside, the other outside the chart area. Unusual, but luckily it fits well the chart. I tried to duplicate the feat with another dataset (similar in structure) and did not even get the second scale to show right, left or center (data plotted with two different scales allright). Where is the info to control these functionalities? Tx, Pierre On Wed, 29 Oct 2003 17:48:13 +0100, pl.carry <pl.carry@wanadoo.fr> wrote= : > Positioning the Vertic...

>> MAIl
This is a multi-part message in MIME format. ------=_NextPart_000_0255_01C5B104.FD22F7C0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable >> MAIl - LIST << INTERNATIONAL WORLDWIDE Business MULTI KLICK HERE: >> www.lux-life.de.vu << >> www.job-dich-reich.de.vu << >> www.luxcash.de.vu << >> www.konto-geld.de.vu << ************************************************************* ------=_NextPart_000_0255_01C5B104.FD22F7C0 Content-Type: te...

plot two groups of numbers on one series of xy coordinates.
how to create a plot from separate x and y values. i.e. y values do not correspond to the same set of x values. example x1= 5, y1=10; x2= 8, y2=15 I can read your question 2 ways; A) you data looks like this x y 5 10 8 15 etc. Select the data and make an XY chart B) you have two sets of data x1 y1 5 10 10 12 17 15 21 20 and x2 y2 3 8 7 14 12 19 18 24 Make an XY chart of the first data series Select all the data (including headers) for second set, and use Copy Click the chart to activate it Use Edit | Paste Special .... indicate New Ser...

Printing Members of Distribution List and Email addresses
Hi Folks, We just recently migrated from Exchange 5.5 to Exchange 2003. Is there an easy way to get a print out of members of a distribution list and their email address? Used to be a bear in 5.5 so I'm hoping MS made it easier in Exchange 2003. Thanks for your help. Zach You'll need to get this information from Active Directory now. I wrote a small and basic script to do this a few years back which you can find here: http://hellomate.typepad.com/exchange/2003/08/dumping_group_m.html I'm sure you can modify this to suit your needs, or have a trawl around for more ad...

two variables
I have a query that asks for collcode, collcode2 and booth. On any given day an employee may be collcode or collcode2. I would like to know how many times a certain emp has been paired with another employee. So how do I ask if an employee has been collcode/collcode2 and who the other person was? I hope this question makes sense. Thanks for any help Query one: SELECT CollCode, CollCode2, Booth FROM YourTable UNION ALL SELECT CollCode2, CollCode, Booth FROM YourTable Query Two SELECT CollCode, CollCode2, Count(CollCode) AS TIMESPAIRED FROM QueryOne GROUP BY CollCode, CollCode2 IF y...

Find two PCI instance but load Init() one time
Hi all, I'm developing a stream interface driver for my PCI device under WinCE 6.0 and this driver works very well currently. Now, I got a problem when testing two the same PCI devices with my stream driver. The registry can find two PCI instances (i.e. PCI\Instance\mydrv1, PCI\ Instance\mydrv2) but system only call my XXX_init() one time. Does anyone know what the problem is? The following information is my registry setting: ================================================================== [HKEY_LOCAL_MACHINE\Drivers\BuiltIn\PCI\Template\mydrv] "Dll"="myd...

Comparing two lists for matches
Hi, I am trying to do the following: There are two sets of supplier lists - A List and B List. I need to compare the two lists. Source A is my master. Source B is a subset of that but has contact information for all the suppliers. Hence, my goal is to retrieve the contact information from Source B and match it to the suppliers of Source A. The above can be easliy achieved using a vlookup funtion. But the problem is that some of the supplier names in the two lists are not exact matches. Hence, vlookup does not catch it. For instance, a supplier is listed as ABC Inc. in A list and as ABC, inc....

Two copies of all Mailboxes
Using my sbs2003 srver with exchange 2003 installed - I notice two copies of all users mail box in the list of mailboxes using outlook 2003 on the same server. When I try to close these I get an error syaing mail boxes in use. I cn not remove them. Can you tell me how to remove these so I have just on mail box visible per user? thx Kevin Cain Class TECHNOLOGY ...

Global Contacts List Issues.
Does anyone know how to synchronize the Global Address List with a users Contacts list? Any and all help would be appreciated. Thanks. Daryl http://www.slipstick.com/exs/portagal.htm might be helpful. "darylakagod" <darylakagod@discussions.microsoft.com> wrote in message news:0EAE59B3-7A70-46A6-8E19-93A533C7E1C1@microsoft.com... > Does anyone know how to synchronize the Global Address List with a users > Contacts list? Any and all help would be appreciated. Thanks. > > Daryl ...

Finding Value of the First Item in a List Box
I need to know the value of the first (OR ONLY) listed item (the value of the first column) in a List Box. My ListBox is named ListOfPeople. "LA Lawyer" <LA Lawyer@discussions.microsoft.com> wrote in message news:ADB3AAD6-913E-4C9A-9E6F-995C97A61512@microsoft.com... >I need to know the value of the first (OR ONLY) listed item (the value of >the > first column) in a List Box. "The first column", or the first row? From what you said, I'm guessing that you mean the first row. > My ListBox is named ListOfPeople. Here's example ...

imported distributions lists show as contacts
Client exported his Contacts containing several Distribution Lists, then imported into a new system. Teh imported DL's show up as separate Contact Lists...how, why, and more importantly, is there a simplified way to correct? Version of Outlook they were exported from and imported into? How did you import them? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Outlook Tips by email: dailytips-subscribe-request@lists.outlooktips.net ...

Compare two lists of names
Can you compare two lists of names where one list is last name first and the other is first name last? try this formual: =RIGHT(TRIM(E2),LEN(E2)-FIND(" ",E2))&" "&TRIM(LEFT(TRIM(E2),FIND(" ",E2))) it will convert First name last name to last name, first name or Visa versa. then you can compare your lists. "ea" wrote: > Can you compare two lists of names where one list is last name first and the > other is first name last? ...