Excel or Access Gurus

Yahoo Archive
1
Assuming that you have all of the 2007 parts on the 2008 Sheet, you can
do a lookup of the 2008 part on the 2007 sheet and if available, bring
in the appropriate column.

Generally I would do a

IF( ISNA( VLOOKUP(lookup_value, table_array, col_index_num,
[range_lookup]) ), " ", VLOOKUP(lookup_value, table_array,
col_index_num, [range_lookup]) ).

The Range Lookup should always be FALSE, this way you don't get the
nearest item. If it's not there you get an N/A so the NA test determines
whether to use the lookup or not.



Now all of the data is on one sheet.





Charlie Smith

Smith Business Services / 2W Technologies LLC

www.vistaconsultant.com <http://www.vistaconsultant.com/> /
www.2WTech.com









From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Jonathan Lang
Sent: Tuesday, March 10, 2009 11:02 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Excel or Access Gurus



Very nice William. I'll have to try bot and see which one works the
best.

Thank you all very much for your inputs. Very Helpfull.

-Jonathan

--- On Tue, 3/10/09, William Hannah <william.hannah@...
<mailto:william.hannah%40fricknet.com> > wrote:

From: William Hannah <william.hannah@...
<mailto:william.hannah%40fricknet.com> >
Subject: RE: [Vantage] Excel or Access Gurus
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Date: Tuesday, March 10, 2009, 9:17 AM

Jonathan,

Another simple way would be to use the SUMIF function. Put the 2008
Data on one sheet with the 2007 data on the other. Use the Part Number
column on the 2007 sheet as the evaluation array and the Part number
cell on the 2008 sheet as the criteria. Have the columns from the 2007
sheet that you want included as the Sum Arrays.

I am sending you a quick example off-list.

Regards,

Bill

William Hannah

Director of Finance

William Frick & Company

Direct: 847.918.7338

www.fricknet. com

____________ _________ _________ __

From: vantage@yahoogroups .com [mailto:vantage@yahoogroups .com] On
Behalf
Of Jonathan Lang
Sent: Tuesday, March 10, 2009 8:57 AM
To: vantage@yahoogroups .com
Subject: Re: [Vantage] Excel or Access Gurus

Sorry, My mail sent itself before I fininshed. I need all of the
information for 2007 and 2008 on each part number. Any ideas?

--- On Tue, 3/10/09, Jonathan Lang <lsbestinc@yahoo. com
<mailto:lsbestinc% 40yahoo.com> > wrote:

From: Jonathan Lang <lsbestinc@yahoo. com <mailto:lsbestinc%
40yahoo.com>
>
Subject: [Vantage] Excel or Access Gurus
To: vantage@yahoogroups .com <mailto:vantage% 40yahoogroups. com>
Date: Tuesday, March 10, 2009, 8:55 AM

It's days like this one that I just want yell, "WHY ME!"

Okay, here it is.

I need to combine two seperate Excel sheets with similar data. Here is
what I have in an example.

2007) Part Num, Acct No, Yr 2007 Price, 2007 Extended Price

2008) Part Num, Acct No, Yr 2008 Price, 2008 Extended Price

I need to have all of the data on the same excel sheet, BUT the part
Numbers have to match up and the 2007 and 2008 data for the part
numbers.

For example using the headings above:

A1234 1000

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

------------ --------- --------- --------- --------- --------- -

William Frick & Co's SmartMarkT RFID Named to 2008 Top Ten Products List

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]
2
It's days like this one that I just want yell, "WHY ME!"
Â
Okay, here it is.
Â
I need to combine two seperate Excel sheets with similar data. Here is what I have in an example.
Â
2007) Part Num, Acct No, Yr 2007 Price, 2007 Extended Price
Â
2008) Part Num, Acct No, Yr 2008 Price, 2008 Extended Price
Â
I need to have all of the data on the same excel sheet, BUT the part Numbers have to match up and the 2007 and 2008 data for the part numbers.
Â
For example using the headings above:
Â
A1234 1000
Â
Â




[Non-text portions of this message have been removed]
3
Sorry, My mail sent itself before I fininshed. I need all of the information for 2007 and 2008 on each part number. Any ideas?

--- On Tue, 3/10/09, Jonathan Lang <lsbestinc@...> wrote:


From: Jonathan Lang <lsbestinc@...>
Subject: [Vantage] Excel or Access Gurus
To: vantage@yahoogroups.com
Date: Tuesday, March 10, 2009, 8:55 AM






It's days like this one that I just want yell, "WHY ME!"
Â
Okay, here it is.
Â
I need to combine two seperate Excel sheets with similar data. Here is what I have in an example.
Â
2007) Part Num, Acct No, Yr 2007 Price, 2007 Extended Price
Â
2008) Part Num, Acct No, Yr 2008 Price, 2008 Extended Price
Â
I need to have all of the data on the same excel sheet, BUT the part Numbers have to match up and the 2007 and 2008 data for the part numbers.
Â
For example using the headings above:
Â
A1234 1000
Â
Â

[Non-text portions of this message have been removed]



















[Non-text portions of this message have been removed]
4
Hi Jonathan,



Likely, you can have each sheet in your workbook separately (2007data,
2008data,CombinedData). Copy whichever worksheet you want as the base
(2007 or 2008) to the CombinedData worksheet...Then use a named range
and vlookup to get data for the other year...



I will send a mini sample direct to you offline.


FYI,

Karen S



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Jonathan Lang
Sent: Tuesday, March 10, 2009 8:57 AM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] Excel or Access Gurus



Sorry, My mail sent itself before I fininshed. I need all of the
information for 2007 and 2008 on each part number. Any ideas?

--- On Tue, 3/10/09, Jonathan Lang <lsbestinc@...
<mailto:lsbestinc%40yahoo.com> > wrote:

From: Jonathan Lang <lsbestinc@... <mailto:lsbestinc%40yahoo.com>
>
Subject: [Vantage] Excel or Access Gurus
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Date: Tuesday, March 10, 2009, 8:55 AM

It's days like this one that I just want yell, "WHY ME!"

Okay, here it is.

I need to combine two seperate Excel sheets with similar data. Here is
what I have in an example.

2007) Part Num, Acct No, Yr 2007 Price, 2007 Extended Price

2008) Part Num, Acct No, Yr 2008 Price, 2008 Extended Price

I need to have all of the data on the same excel sheet, BUT the part
Numbers have to match up and the 2007 and 2008 data for the part
numbers.

For example using the headings above:

A1234 1000



[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]
5
What will the vlookup function return if there is not a match on one of the
rows?

Jeff



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
Jonathan Lang
Sent: Tuesday, March 10, 2009 9:57 AM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] Excel or Access Gurus



Sorry, My mail sent itself before I fininshed. I need all of the information
for 2007 and 2008 on each part number. Any ideas?

--- On Tue, 3/10/09, Jonathan Lang <lsbestinc@...
<mailto:lsbestinc%40yahoo.com> > wrote:

From: Jonathan Lang <lsbestinc@... <mailto:lsbestinc%40yahoo.com> >
Subject: [Vantage] Excel or Access Gurus
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Date: Tuesday, March 10, 2009, 8:55 AM

It's days like this one that I just want yell, "WHY ME!"

Okay, here it is.

I need to combine two seperate Excel sheets with similar data. Here is what
I have in an example.

2007) Part Num, Acct No, Yr 2007 Price, 2007 Extended Price

2008) Part Num, Acct No, Yr 2008 Price, 2008 Extended Price

I need to have all of the data on the same excel sheet, BUT the part Numbers
have to match up and the 2007 and 2008 data for the part numbers.

For example using the headings above:

A1234 1000



[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]
6
Jonathan,



Another simple way would be to use the SUMIF function. Put the 2008
Data on one sheet with the 2007 data on the other. Use the Part Number
column on the 2007 sheet as the evaluation array and the Part number
cell on the 2008 sheet as the criteria. Have the columns from the 2007
sheet that you want included as the Sum Arrays.



I am sending you a quick example off-list.



Regards,



Bill



William Hannah

Director of Finance

William Frick & Company

Direct: 847.918.7338

www.fricknet.com

________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Jonathan Lang
Sent: Tuesday, March 10, 2009 8:57 AM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] Excel or Access Gurus



Sorry, My mail sent itself before I fininshed. I need all of the
information for 2007 and 2008 on each part number. Any ideas?

--- On Tue, 3/10/09, Jonathan Lang <lsbestinc@...
<mailto:lsbestinc%40yahoo.com> > wrote:

From: Jonathan Lang <lsbestinc@... <mailto:lsbestinc%40yahoo.com>
>
Subject: [Vantage] Excel or Access Gurus
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Date: Tuesday, March 10, 2009, 8:55 AM

It's days like this one that I just want yell, "WHY ME!"

Okay, here it is.

I need to combine two seperate Excel sheets with similar data. Here is
what I have in an example.

2007) Part Num, Acct No, Yr 2007 Price, 2007 Extended Price

2008) Part Num, Acct No, Yr 2008 Price, 2008 Extended Price

I need to have all of the data on the same excel sheet, BUT the part
Numbers have to match up and the 2007 and 2008 data for the part
numbers.

For example using the headings above:

A1234 1000



[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]




--------------------------------------------------------------------------

William Frick & Co's SmartMarkT RFID Named to 2008 Top Ten Products List


[Non-text portions of this message have been removed]
7
> What will the vlookup function return if there is not a match on one of
> the rows?

The last parameter of the VLOOKUP function determines that behavior. It will
either return the closest record or NULL.

The VLOOKUP will work only if the every part number is in both sheets. If a
part exists in target but not your source then you'll miss it completely.

If the number of records isn't too great, you could dump both sheets into
one adding a field for the year then take that sheet and do a pivot table.
That will give you all of the data in a single view of the data.

Mark W.
8
THANK YOU!. Awesome.

--- On Tue, 3/10/09, Karen Schoenung <kschoenung@...> wrote:


From: Karen Schoenung <kschoenung@...>
Subject: RE: [Vantage] Excel or Access Gurus
To: vantage@yahoogroups.com
Date: Tuesday, March 10, 2009, 9:04 AM






Hi Jonathan,

Likely, you can have each sheet in your workbook separately (2007data,
2008data,CombinedDa ta). Copy whichever worksheet you want as the base
(2007 or 2008) to the CombinedData worksheet... Then use a named range
and vlookup to get data for the other year...

I will send a mini sample direct to you offline.

FYI,

Karen S

From: vantage@yahoogroups .com [mailto:vantage@yahoogroups .com] On Behalf
Of Jonathan Lang
Sent: Tuesday, March 10, 2009 8:57 AM
To: vantage@yahoogroups .com
Subject: Re: [Vantage] Excel or Access Gurus

Sorry, My mail sent itself before I fininshed. I need all of the
information for 2007 and 2008 on each part number. Any ideas?

--- On Tue, 3/10/09, Jonathan Lang <lsbestinc@yahoo. com
<mailto:lsbestinc% 40yahoo.com> > wrote:

From: Jonathan Lang <lsbestinc@yahoo. com <mailto:lsbestinc% 40yahoo.com>
>
Subject: [Vantage] Excel or Access Gurus
To: vantage@yahoogroups .com <mailto:vantage% 40yahoogroups. com>
Date: Tuesday, March 10, 2009, 8:55 AM

It's days like this one that I just want yell, "WHY ME!"

Okay, here it is.

I need to combine two seperate Excel sheets with similar data. Here is
what I have in an example.

2007) Part Num, Acct No, Yr 2007 Price, 2007 Extended Price

2008) Part Num, Acct No, Yr 2008 Price, 2008 Extended Price

I need to have all of the data on the same excel sheet, BUT the part
Numbers have to match up and the 2007 and 2008 data for the part
numbers.

For example using the headings above:

A1234 1000



[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]



















[Non-text portions of this message have been removed]
9
Very nice William. I'll have to try bot and see which one works the best.
Â
Thank you all very much for your inputs. Very Helpfull.
Â
-Jonathan

--- On Tue, 3/10/09, William Hannah <william.hannah@...> wrote:


From: William Hannah <william.hannah@...>
Subject: RE: [Vantage] Excel or Access Gurus
To: vantage@yahoogroups.com
Date: Tuesday, March 10, 2009, 9:17 AM






Jonathan,

Another simple way would be to use the SUMIF function. Put the 2008
Data on one sheet with the 2007 data on the other. Use the Part Number
column on the 2007 sheet as the evaluation array and the Part number
cell on the 2008 sheet as the criteria. Have the columns from the 2007
sheet that you want included as the Sum Arrays.

I am sending you a quick example off-list.

Regards,

Bill

William Hannah

Director of Finance

William Frick & Company

Direct: 847.918.7338

www.fricknet. com

____________ _________ _________ __

From: vantage@yahoogroups .com [mailto:vantage@yahoogroups .com] On Behalf
Of Jonathan Lang
Sent: Tuesday, March 10, 2009 8:57 AM
To: vantage@yahoogroups .com
Subject: Re: [Vantage] Excel or Access Gurus

Sorry, My mail sent itself before I fininshed. I need all of the
information for 2007 and 2008 on each part number. Any ideas?

--- On Tue, 3/10/09, Jonathan Lang <lsbestinc@yahoo. com
<mailto:lsbestinc% 40yahoo.com> > wrote:

From: Jonathan Lang <lsbestinc@yahoo. com <mailto:lsbestinc% 40yahoo.com>
>
Subject: [Vantage] Excel or Access Gurus
To: vantage@yahoogroups .com <mailto:vantage% 40yahoogroups. com>
Date: Tuesday, March 10, 2009, 8:55 AM

It's days like this one that I just want yell, "WHY ME!"

Okay, here it is.

I need to combine two seperate Excel sheets with similar data. Here is
what I have in an example.

2007) Part Num, Acct No, Yr 2007 Price, 2007 Extended Price

2008) Part Num, Acct No, Yr 2008 Price, 2008 Extended Price

I need to have all of the data on the same excel sheet, BUT the part
Numbers have to match up and the 2007 and 2008 data for the part
numbers.

For example using the headings above:

A1234 1000



[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

------------ --------- --------- --------- --------- --------- -

William Frick & Co's SmartMarkT RFID Named to 2008 Top Ten Products List

[Non-text portions of this message have been removed]



















[Non-text portions of this message have been removed]
10
If it'll all fit on one sheet, do that & then create a pivot table (part number as y axis & year as x axis. Then dump your price and extended price into the table.

Rob

--- On Tue, 3/10/09, Jonathan Lang <lsbestinc@...> wrote:

From: Jonathan Lang <lsbestinc@...>
Subject: [Vantage] Excel or Access Gurus
To: vantage@yahoogroups.com
Date: Tuesday, March 10, 2009, 9:55 AM






It's days like this one that I just want yell, "WHY ME!"
Â
Okay, here it is.
Â
I need to combine two seperate Excel sheets with similar data. Here is what I have in an example.
Â
2007) Part Num, Acct No, Yr 2007 Price, 2007 Extended Price
Â
2008) Part Num, Acct No, Yr 2008 Price, 2008 Extended Price
Â
I need to have all of the data on the same excel sheet, BUT the part Numbers have to match up and the 2007 and 2008 data for the part numbers.
Â
For example using the headings above:
Â
A1234 1000
Â
Â

[Non-text portions of this message have been removed]