Sum uniques based on a condition, with text string parsing

[TomYC]

Here is a problem that has bedeviled me for a couple of years, along with a colleague (Ref:1340224).
My data is exported out of a web-based database that is obviously not really relational, thus there are duplicate record (ID#s) with differing columnar data. My task is to be able to count (in a separate worksheet) the number of unique ID#s with the condition that a particular column contains one of various text strings. Apparently the checkboxes on the database result in choiceA|choiceB text appearing in this column.
Here is a simplified representation of the Excel data:
ID Date Result
PR-09375 2/19/2010 Refund|Letter|Other
PR-09375 2/19/2010 Repair|Letter|Refer
PR-09376 3/2/2010 Repair|Letter
PR-09377 3/3/2010 Repair|Letter|Refer
PR-09377 3/3/2010 Repair|Letter|Satisfy
In my summary worksheet, I would hope to see something as:
Result Feb Mar
Letter 1 2
Other 1 0
Refer 1 1
Refund 1 0
Repair 1 2
Satisfy 0 1

In other words, I need at the same time--a)date range, b)unique record ID, and c)incidence of text string.
Can this be done in Excel?
Tnx

 

[SkipVought]

Hi,

Got this PivotTable result in about 3 min...
[tt]
Count of Value
ID Date Value Total
PR-09375 2/19/2010 Letter 2
Other 1
Refer 1
Refund 1
Repair 1
PR-09376 3/2/2010 Letter 1
Repair 1
(blank)
PR-09377 3/3/2010 Letter 2
Refer 1
Repair 2
Satisfy 1
[/tt]
after producing this table using this general process: faq68-5287
[tt]
STEPS:
1. INSERT a column between Date & Results

2. CONCATENATE ID & Date, separated by a |...
=A2&"|"&B2

3. PARSE Result on |

4. Make the headings Result1, Result2... like

ID Date [highlight]ID_Date Result1 Result2 Result3[/highlight]
PR-09375 2/19/2010 [highlight]PR-09375|40228 Refund Letter Other[/highlight]
PR-09375 2/19/2010 [highlight]PR-09375|40228 Repair Letter Refer[/highlight]
PR-09376 3/2/2010 [highlight]PR-09376|40239 Repair Letter[/highlight]
PR-09377 3/3/2010 [highlight]PR-09377|40240 Repair Letter Refer[/highlight]
PR-09377 3/3/2010 [highlight]PR-09377|40240 Repair Letter Satisfy[/highlight]

5. NOW use faq68-5287. When you get to Setp 2b, select the range to ADD, as indicated above. Complete the process.

6. The result is a tabel like this

Row Column Value
PR-09375|40228 Result1 Refund
PR-09375|40228 Result2 Letter
PR-09375|40228 Result3 Other
PR-09375|40228 Result1 Repair
PR-09375|40228 Result2 Letter
PR-09375|40228 Result3 Refer
PR-09376|40239 Result1 Repair
PR-09376|40239 Result2 Letter
PR-09376|40239 Result3
PR-09377|40240 Result1 Repair
PR-09377|40240 Result2 Letter
PR-09377|40240 Result3 Refer
PR-09377|40240 Result1 Repair
PR-09377|40240 Result2 Letter
PR-09377|40240 Result3 Satisfy

7. CLEAR the data in the column labled COLUMN

8. Parse ID_Date on |

9. Format your Dates as requied

10. Rename all your columns as desired.
[/tt]
NOW you are ready to summarize your data, using a PivotTabel or any other Excel aggregation feature.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TomYC]

I'm game, and so I'm parsing--I may have as many as 10 items to nest, so if anyone can direct to a nesting parsing faq I'm grateful!

 

[SkipVought]

...10 items to nest,...

THEN, after you PARSE the Result column, you will have 10 columns by however many rows. It ALL works the same!


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Here's one I just did with 13 items in Result...
[tt]
Count of Value
Row Column1 Value Total
PR-093752/19/2010Letter 2
Other 1
Other2 1
Other3 1
Other4 1
Other5 1
Other6 1
Other7 1
Other8 1
Other9 1
Refer 1
Refund 1
Repair 1
PR-09376 3/2/2010Letter 1
Repair 1
PR-09377 3/3/2010Letter 2
Refer 1
Repair 2
Satisfy 1
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TomYC]

That's just great, and I hope to get there two, but for me it is one column at a time, thus I am currently at:
LEFT(U2,FIND("|",U2,1)-1)
as a value for Results1, in cell V2, in which case I have:
MID(U2,LEN(V2)+2,FIND("|",U2,LEN(V2)+2)-LEN(V2)-2)
as a value for Results2, in cell W2, etc., but it's getting pretty hairy...
which is why I'm hoping there is a standard answer for parsing:
U2=text1|text2|text3| etc. !!

 

[SkipVought]

Do don't need to use those text function.

Use Data > Text to columns and PARSE!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

BTW, I used only ONE formula: the one that concatenates ID & Date.

I performed the entire process, as described in a previous post, at least 4 times this afternoon. It's pretty easy.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}