Finding/deleting duplicates and merging cells

[Louise]

Hi all

A colleague of mine has a large worksheet with data on it that has been
imported from another application into columns A and B in Excel. Column A,
for argument's sake, contains a cost centre number and Column B contains text
relating to that cost centre number. The text in column B isn't imported into
one cell, it is spread over several rows - one line of text per row. Because
of this, the information in Column A is duplicated for the same number of
rows. For example:-

A B

1 cc 1001 This cost centre number
2 cc 1001 is for Region 2 and
3 cc 1001 was introduced September 05
4 cc 1001 to be used until further notice.

This is what they need to do............. Remove the duplicate information
in Column A (rows 2-4) and merge the information n B1:B4 into one cell.

I realise we can go through and do this manually by simply deleting the
cells etc but can anybody advise me of an easier way to do this? Otherwise,
it will take hours.

Any help would be greatly appreciated.

Thank you.

Louise

 

[Roger Govier]

Hi Louise

The following is a fairly inelegant solution, but it is quite quick to
do and achieves the desired result.
! am assuming here that the maximum lines per cc is 4, if it greater it
will be just a case of inserting an extra column into the following
instructions for each extra row that the data per cc contains.

With data in columns A and B with headers assumed to be in Row 1
in D2 =IF(A2=A1,"",A2) in E2 =IF(A2=A1,"",B2) in F2 =IF(A3=A2,B3,"")
in G2 =IF(A4=A3,B4,"") in H2 =IF(A5=A4,B5,"")

If there are more than 4 lines per CC, then insert more columns at this
point, and move the following section along by the number of columns
inserted.

in J2 =D2
in K2 =E2&" "&F2&" "&G2&" "&H2
Again, if you use more columns, then keep adding &" "& column

Copy all the formulae in D2:K2 down to cover the range of rows in A
having data.
(It will look very messy, but don't worry!!)
Copy the complete block of data from J2:Knn where nn is the last row of
data.
Place cursor in J2, Paste Special>Values
Mark block of data in columns J and K >Data>Sort> Column J >Ascending
All the "dross" will drop to the bottom.
Copy the range of "good" data to another location.

Other may post more sophisticated formulae using offsets which will
avoid the untidy looking mess, and avoid the sort but I don't have time
to work that out right now, and this should work OK for you.