Comparing Table or Ranges of Data

  • Thread starter Thread starter Novice
  • Start date Start date
N

Novice

I have 2 ranges (tables) comprised of multiple columns of data.
However, one column of data for each table or range contains the same
field, but not always the same number of elements. Without using
Access, how do I generate an output table that contains only the common
data and corresponding information? Here's what I'm talking about:

Table or Range #1
Store # Sales$
1 1000
2 1200
3 500


Table or Range #2
Store # Address City State
1 Main Street Wherever NY
3 Elm Street Bumble MD
4 Peachtree Street Atlanta GA
5 Rt 66 Desert AZ

So, how do I "merge" the data so I can generate a report that only
delineates the common stores but yet includes all of the fields; i.e.,
as follows:

Store # Address City State
Sales$
1 Main Street Wherever NY
1000
3 Elm Street Bumble MD
500

I know how to do it using MS Access, but that is a pain - having to
create the Tables, importing, etc.

Thanks for your assistance.
 
You could create a combined table using VLOOKUP. Assuming your tables have been named, you can put the store number in A2 and the following formulas in B2 thru E

=VLOOKUP($A2,Table1,2,0
=VLOOKUP($A2,Table2,2,0
=VLOOKUP($A2,Table2,3,0
=VLOOKUP($A2,Table2,4,0

You can find the structure of the VLOOKUP function in the Excel Help files

The $ if front of the A makes the column reference absolute. This prevents it from changing when you copy the formula

Table1 and Table2 can be replaced by the actual range references of the data cells, or you can select the ranges and define them under Insert>Name>Define

If you copy this down you will create a table which includes every store

You can then use Data>Filter>Auto_Filter to hide the stores that were not on each list. The easiest way is to use (custom...)>does not equal>#N/A

If you have any problems or questions post back in this thread

Good Luck
Mark Graesse
(e-mail address removed)


----- Novice wrote: ----

I have 2 ranges (tables) comprised of multiple columns of data.
However, one column of data for each table or range contains the sam
field, but not always the same number of elements. Without usin
Access, how do I generate an output table that contains only the commo
data and corresponding information? Here's what I'm talking about

Table or Range #
Store # Sales
1 100
2 120
3 50


Table or Range #
Store # Address City Stat
1 Main Street Wherever N
3 Elm Street Bumble M
4 Peachtree Street Atlanta G
5 Rt 66 Desert A

So, how do I "merge" the data so I can generate a report that onl
delineates the common stores but yet includes all of the fields; i.e.
as follows

Store # Address City State
Sales
1 Main Street Wherever NY
100
3 Elm Street Bumble MD
50

I know how to do it using MS Access, but that is a pain - having t
create the Tables, importing, etc

Thanks for your assistance
 
Back
Top