Help with cross-referencing

  • Thread starter Thread starter czechboy16
  • Start date Start date
C

czechboy16

I have a spreadsheet with two columns containing hundreds of computer
names, and I would like to sort the columns so that if they both
contain the same computer name, they will match-up in one row. IE, if
both columns contain the value "computer1" then both columns will
contain the value "computer1" in the same row. Whereas computer names
that don't match up will have an empty cell next to them.

Is there an easy way to do this in Excel?

Thanks!
 
How about this....

Insert a new column B
Put "first" in column B for all the rows with a computer name in column A.
Put "second" in column D for all the rows with computer name in column C.

Move the data from columns C&D to the bottom of columns A&B.

Put headers in row 1 (if you don't have them already)
A1: ComputerName
B1: List

Now you have one giant list with two columns.

Select your range of data A1:B####

Data|Pivottable...
follow the wizard until you get to a step that has a "Layout" button on it.
Hit that Layout button.

Drag the header for the ComputerName field to the Row field
drag the header for List to the column field field
drag the header for List to to the data field
If you don't see "Count of" in that data field, then double click on it and make
choose "Count of"

I would stop there!

But if you really want your layout, keep going...

Ctrl-a (twice in xl2003) to select all the cells
edit|copy followed by edit|paste special|values

select the data in the First and Second columns (they should all be 1's if there
were no duplicates in each list).

Edit|goto|special|check constants
type
=$a5
(5 is the row that has the activecell)
but hit ctrl enter instead of just enter.

Select that range again.
edit|copy followed by edit|paste special|values
Clean up the rows/columns that you don't want.

If you want to read more about the pivottable stuff, you may want to look at
some links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx
 
Dave-

Wow! Thanks for taking the time to reply. That worked great! That'll
save me LOTS of time. I do that sort of cross-referencing a lot, I
figured there was some way to automate it...

Thanks again!!!
 
Back
Top