How to avoid duplicating entries, etc.

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

hello,

I have a worksheet containing the details of 5000 visits
by 500 patients being treated at our clinic. My problem is
that our listing is by visit, rather than by patient. That
is, Column A contains multiple instances (1-30) of each
patients' unique ID number.

I am hoping to alter the worksheet so that each patient is
only represented once, and that all visits by the same
patient are listed in the same row

Thus:
Column A: ID Number
Column B: Date of Visit 1
Column C, D, etc: Date of Visit 2, 3, etc

Any tips would be greatly appreciated
Thanks
Paul, Sydney.
 
Hello Paul

Here's one way to do it:

Assuming your list in A2:B100, ID's in A2:A100
and dates in B2:B100 (change references to the actual setup)

1. In F2 enter the formula on one line (F1 must not contain data present
in column A):
=INDEX($A$2:$A$100,MIN(IF(COUNTIF($F$1:F1,$A$2:$A$100)=
0,ROW($A$2:$A$100)-ROW($A$2)+1)))

2. In G2 enter this array formula on one line:
=IF(COUNTIF($A$2:$A$100,$F2)< COLUMN()-COLUMN($F$2),"",
INDEX($B$2:$B$100,MIN(IF(($A$2:$A$100=$F2)*
(COUNTIF(INDIRECT("A2:A" & ROW($A$2:$A$100)),$F2)=
COLUMN()-COLUMN($F$2)),ROW($A$2:$A$100)-ROW($A$2)+1))))

Please notice the used of mixed absolute($$) and relative (no cash)
references.
The formula must be entered with <Shift><Ctrl><Enter> also if edited later.
If done correctly, Excel will display the formula in the formula bar
enclosed
in curly brackets { } Don't enter these brackets yourself.

3. Drag G2 to AJ2 with the fill handle (the little square in the lower
right corner of the cell)

4. Select F2:AJ2 and drag the selection to F5001:AJ5001 with the fill
handle.

5. With the block selected, copy it with e.g. <Ctrl>c

6. Choose Edit > Paste special > Values (or similar)

All formulae are now gone from F2:AJ5001, and your new setup is finished
with IDs in column F and matching visiting dates in columns G through AJ..

--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.
 
Back
Top