Need some help on some text handeling

  • Thread starter Thread starter adam_kroger
  • Start date Start date
A

adam_kroger

Using Excel 2003

I have a series of ~20 cells that may or may not have text in them
i.e.
A1:A5 - Report, Meeting, Reply, Recon, <>
B1:B5 - Meeting, Excel Project, Reply, <>, <>
C1:C5 - Excel Project, <>, <>, <>, <>
D1:D5 - Meeting, Apples, <>, <>, <>

<> denotes an empty cell

I would like to have something like:
"Report, Meeting(3), Reply(2), Recon, Excel Project(2),
Apples"
to appear in another Cell. If need be, the count of the repeated
items can be dropped.

PURPOSE: a weekly synopsis of data entered as "Miscellaneous" on a
daily tracking sheet (each day has space for 4 items), Ideal solution
would also allow this to be reported on a quarterly, semi-annual, and
annual basis also; though that may be too ambitious/resource intensive.
 
Do you want a macro to accomplish this or do you simply want formulas that
you can place on a spreadsheet to manipulate the data. Both are possible but
with formulas on your worksheet, it would require some initial setting up and
then minimal mainiplation each time the data changes mainly due to the
unknown number of blank cells. The count of repeated items can be included.

Regards,

OssieMac
 
Say you have a list of texts in J1:J6, then use this formula in any cells you
want:

=J1&"("&SUMPRODUCT(--(A1:D5=J1))&"),"&J2&"("&SUMPRODUCT(--(A1:D5=J2))&"),"

Go on in this way with J3 to J6

Regards,
Stefi

„[email protected]†ezt írta:
 
Back
Top