Summarize Data Problem

Boanerges_0800

New Member
Joined
Feb 22, 2012
Messages
17
I have a list of data that has persons employee number and names in the columns A:B and activities they participated in row 1 (ex: C1- hiking, D1-running, E1-skating, F1-walking, and H1-lifting) listed across the top of the spreadsheet. Starting in cell C2, each person could have any combination of activities listed with their name and number including none. </SPAN>

[TABLE="width: 897"]
<TBODY>[TR]
[TD][/TD]
[TD]A</SPAN>
[/TD]
[TD]B</SPAN>
[/TD]
[TD]C</SPAN>
[/TD]
[TD]D</SPAN>
[/TD]
[TD]E</SPAN>
[/TD]
[TD]F</SPAN>
[/TD]
[TD]G</SPAN>
[/TD]
[TD]H</SPAN>
[/TD]
[TD]I</SPAN>
[/TD]
[TD]J</SPAN>
[/TD]
[TD]K</SPAN>
[/TD]
[TD]L</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1</SPAN>
[/TD]
[TD]Num</SPAN>
[/TD]
[TD]Nam</SPAN>
[/TD]
[TD]Hiking</SPAN>
[/TD]
[TD]Running</SPAN>
[/TD]
[TD]Skating </SPAN>
[/TD]
[TD]Walking</SPAN>
[/TD]
[TD]Lifting</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2</SPAN>
[/TD]
[TD="align: right"]123</SPAN>
[/TD]
[TD]John J</SPAN>
[/TD]
[TD]Hiking</SPAN>
[/TD]
[TD]Running</SPAN>
[/TD]
[TD][/TD]
[TD]Walking</SPAN>
[/TD]
[TD][/TD]
[TD]Hiking</SPAN>
[/TD]
[TD]Running</SPAN>
[/TD]
[TD]Walking</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3</SPAN>
[/TD]
[TD="align: right"]459</SPAN>
[/TD]
[TD]Susan B</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Skating </SPAN>
[/TD]
[TD]</SPAN>
[/TD]
[TD]Lifting</SPAN>
[/TD]
[TD]Skating </SPAN>
[/TD]
[TD]</SPAN>Lifting
[/TD]
[TD]</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]


I want excel to summarize text found in Columns C-H and enter it into columns H,I,J,K,L skipping any blanks. For example if John J’s data was Hiking, Running & Walking than the result would be H2-Hiking,I2-Running & J2-Walking. If Susan B’s data was Skating and Lifting then the result would be H3-Skating, I3-Lifting, and columns J-L would be blank.


Thanks for your help. I am using Excel 2010</SPAN>
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Try this:-
Code:
[COLOR="Navy"]Sub[/COLOR] MG17Aug54
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Temp        [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Ac          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] c
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B2"), Range("B" & Rows.count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    c = 5
    [COLOR="Navy"]Set[/COLOR] Temp = Dn.Offset(, 1).Resize(, 5).SpecialCells(xlCellTypeConstants)
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Ac [COLOR="Navy"]In[/COLOR] Temp
            c = c + 1
            Dn.Offset(, c) = Ac
        [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Hi Mick,
Thanks for the answer. I am having a couple of problems (I should have mentioned #2 & 3 previously)

1.) The macro only ran for the first three rows (see below)
2.) Will it work for a blank row?
2.) The data I'm using the solution on is from column A to AN (40 columns wide) and 1 to 400 rows



[TABLE="width: 640"]
<TBODY>[TR]
[TD]Num</SPAN>
[/TD]
[TD]Nam</SPAN>
[/TD]
[TD]Hiking</SPAN>
[/TD]
[TD]Running</SPAN>
[/TD]
[TD]Skating </SPAN>
[/TD]
[TD]Walking</SPAN>
[/TD]
[TD]Lifting</SPAN>
[/TD]
[TD]Summary</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]123</SPAN>
[/TD]
[TD]John J</SPAN>
[/TD]
[TD]Hiking</SPAN>
[/TD]
[TD]Running</SPAN>
[/TD]
[TD][/TD]
[TD]Walking</SPAN>
[/TD]
[TD][/TD]
[TD]Hiking</SPAN>
[/TD]
[TD]Running</SPAN>
[/TD]
[TD]Walking</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]459</SPAN>
[/TD]
[TD]Susan B</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Skating </SPAN>
[/TD]
[TD]Walking</SPAN>
[/TD]
[TD]Lifting</SPAN>
[/TD]
[TD]Skating </SPAN>
[/TD]
[TD]Walking</SPAN>
[/TD]
[TD]Lifting</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]460</SPAN>
[/TD]
[TD]Jeff</SPAN>
[/TD]
[TD][/TD]
[TD]Running</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Lifting</SPAN>
[/TD]
[TD]Running</SPAN>
[/TD]
[TD]Lifting</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]461</SPAN>
[/TD]
[TD]Rick</SPAN>
[/TD]
[TD]Hiking</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]462</SPAN>
[/TD]
[TD]Mick</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Skating </SPAN>
[/TD]
[TD]Walking</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]461</SPAN>
[/TD]
[TD]Jerry</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]


Kind regards,
JB
 
Upvote 0
Hey Mick,
I fixed problem 1.) above but still have a problem with blank rows (the macro errors) and how to extend it to read a range A to AN (40 columns wide) and 1 to 400 rows.

I sincerely appreciate your help.
Regards,
JB
 
Upvote 0
Probably small after match but this wil be formula approach:

Excel 2010
ABCDEFGHANAOAPAQ
1NumNamHikingRunningSkatingWalkingLifting
2123John JHikingRunningWalkingHikingRunningWalking
3459Susan BSkatingLiftingSkatingLifting
Sheet2
Cell Formulas
RangeFormula
AO2{=IFERROR(INDEX($C2:$AN2,,SMALL(IF($C2:$AN2<>"",COLUMN($C$2:$AN$2)-COLUMN($C$2)+1),COLUMNS($C$2:C2))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Hi Robert,
Thanks for the response. I can't get it to work in excel 2010. I am using the same format you have above (cells and rows with the formula in cell AO2. When I copy the formula and select the cell (ctrl+shift+enter) excel simply shows the formula and does nothing (as if its text). If I take the outside brackets off it just has a blank cell.

I appreciate your help. What am I doing wrong?
Thanks,
JB
 
Upvote 0
Hi Robert,
Thanks for the great example. I got it to work on my real data. I didn't understand the "ctrl+alt+enter" necessity to activate the array until I saw your example and it worked on blank cells. My problem was i didn't remove the "0" representing no activity with " " but after removing them no problems.

You are the Man Today!!!

Thanks so much for working on this for me. Have a great weekend!!!

Best regards,
JB
 
Upvote 0
Hi Robert,
Thanks for the great example. I got it to work on my real data. I didn't understand the "ctrl+alt+enter" necessity to activate the array until I saw your example and it worked on blank cells. My problem was i didn't remove the "0" representing no activity with " " but after removing them no problems.

You are the Man Today!!!

Thanks so much for working on this for me. Have a great weekend!!!

Best regards,
JB

You are welcome.
Thanks for feedback.
 
Upvote 0

Forum statistics

Threads
1,225,361
Messages
6,184,510
Members
453,237
Latest member
lordleo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top