Which function is most appropriate?

  • Thread starter Thread starter lingo
  • Start date Start date
L

lingo

Sheet one consists of 32,000+ rows and two columns. Column A contains a
list of specific serial numbers. Column B contains a list of business
units assigned to those serial numbers. The serial numbers in colmun A
can be repeated several times depending on how many different business
units are assigned to it.

For example

Code:
--------------------

COLUMN A | COLUMN B
#000112 | UnitA
#000112 | UnitC
#000114 | UnitA
#000125 | UnitA
#000125 | UnitB
#000125 | UnitC

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


My goal is to consolidate that data onto sheet 2. In sheet 2, row 1
would contain the first serial number, row 2 the second etc. The
columns would each contain the individual business units. I need to go
along the row and add a hashmark in each business unit's column to
which that serial number is assigned i.e.


Code:
--------------------

COLUMN A | UnitA | UnitB | UnitC
#000112 | X | | X
#000114 | X | |
#000125 | X | X | X

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


Is there an easier way to do this aside from the manual way? Like I
said, I have over 32,000 rows of data... my eyes are begining to cross
from staring at this spreadsheet.
 
A starting point would be to copy column A to a new sheet, give it a
heading in A1 like "Number", then apply Data | Filter | Advanced Filter
to the column to obtain Unique records only and filter in place.

Then with the headings in B1 to D1 as shown in your example, you enter
this formula in B2 of the new sheet:

=SUM(IF(($A2=Sheet1!$A$2:$A$32000)*(B$1=Sheet1!$B$2:$B$32000),1,0))

The ranges should be adjusted if not exactly 32000. This is an array
formula, so when you have typed it in (and whenever you edit it) you
should use CTRL-SHIFT-ENTER instead of the normal ENTER, and if you are
successful then Excel will have wrapped curly braces { } around the
formula - do not type these yourself.

The formula can be copied across to D2, and then B2:D2 can be copied
down for as many entries as you have in column A. This should give you
a series of 1's and 0's under the unit columns, though you may have 2,
3 or 4 etc if you had duplicate entries in your original table.
Highlight all the data from B2 to D whatever, then click <copy> Edit |
Paste Special | Values (check) OK and <Esc>to fix the values.

Now you can do Find and Replace (CTRL-H) on the highlighted block to
replace 1 with whatever symbol you like and you can replace 0 with
nothing. Bit quicker than doing it manually.

Hope this helps.

Pete
 
Throw a pivot table at it. Drag Column A field to ROW DATA, Column B field
to COLUMN DATA, and then Column B field again into the DATA area.


--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------­------------------------------­----------------
 
I tried the pivot table, but I keep getting an error saying column A
contains too many unique items... any suggestions on how to get around
that?
 
Not if you've hit that limit. Stick with Pete's solution.

Regards
Ken................
 
Back
Top