I need to create a unique data from a spreadsheet with the design as below:

1/1/07 2/1/07 3/1/07 ...... (date at row 1)

9am abc xyz (empty)
11am rst abc xyz
2pm xyz rst (empty)

The expected result is:

abc 2
rst 2
xyz 3

which show the unique data from the table with the number it appeards.

Thank you for your help in advance.

Try

=Countif(B2:D4,"xyz")

or, with xyz in cell A10

=Countif(B2:D4,A10)


"PL" wrote:

I need to create a unique data from a spreadsheet with the design as below:

1/1/07 2/1/07 3/1/07 ...... (date at row 1)

9am abc xyz (empty)
11am rst abc xyz
2pm xyz rst (empty)

The expected result is:

abc 2
rst 2
xyz 3

which show the unique data from the table with the number it appeards.

Thank you for your help in advance.

Hi Duke,

The table was actually consists of more than 50 items which I may not know
thru out the 3 months (1/1/07 to 31/3/07). Hence need some solution to
capture out the list of 50 items or more from the worksheet, then count out
the nos. of individual items had appeared in the table thru this 3 months.

Hope this will make clear to you.

"Duke Carey" wrote:

Try

=Countif(B2:D4,"xyz")

or, with xyz in cell A10

=Countif(B2:D4,A10)


"PL" wrote:

I need to create a unique data from a spreadsheet with the design as below:

1/1/07 2/1/07 3/1/07 ...... (date at row 1)

9am abc xyz (empty)
11am rst abc xyz
2pm xyz rst (empty)

The expected result is:

abc 2
rst 2
xyz 3

which show the unique data from the table with the number it appeards.

Thank you for your help in advance.

PL -

The issue that you run into with the data layout you have displayed is that
analysis on that data is very hard to perform. You are far, FAR better off
to structure your data like so (using your example):

Column 1 = Date
Column 2 = Time
Column 3 = value

Now you can easily generate a list of unique data items, use that list of
unique items to generate pivot tables and other reports that analyze the data.

Sometimes a manager says you just HAVE to keep things layed out the way
you've shown, but that lay out is a simple pivot table off the 3-column
structure I suggested. The analysis you want to perform here is also
possible off that structure.

If you restructure your worksheet that way, you can complete the analysis is
less than 20 seconds


"PL" wrote:

Hi Duke,

The table was actually consists of more than 50 items which I may not know
thru out the 3 months (1/1/07 to 31/3/07). Hence need some solution to
capture out the list of 50 items or more from the worksheet, then count out
the nos. of individual items had appeared in the table thru this 3 months.

Hope this will make clear to you.

"Duke Carey" wrote:

Try

=Countif(B2:D4,"xyz")

or, with xyz in cell A10

=Countif(B2:D4,A10)


"PL" wrote:

I need to create a unique data from a spreadsheet with the design as below:

1/1/07 2/1/07 3/1/07 ...... (date at row 1)

9am abc xyz (empty)
11am rst abc xyz
2pm xyz rst (empty)

The expected result is:

abc 2
rst 2
xyz 3

which show the unique data from the table with the number it appeards.

Thank you for your help in advance.