Lookup,Count or any goes

In your example all the same numbers are grouped together. That is, under '2' all the 3's are together and all the 1's are together. Is that always the case or could they be all mixed up?

Does the answer {eg (3)3",(1)4",(2)1" } all go in one cell?

ya it goes in one cell dear,

needhelp2 said:

ya it goes in one cell dear,

Click to expand...

That answers my second question. What about my first one?

Ya all are place together. 33333,222,1 ect. under single head.they wont mixed up.

Dear Peter is it possible to get from script or through formula for it?

I would appreciate if its done.

Thanks in Advance.

thanks

needhelp2 said:

I have two sheets In workbook.

Click to expand...

We don't know what they are called. I have assumed that the one with the table of data is called 'Data'. Change this name in the code to suit your sheet name.

needhelp2 said:

.. if i enter value 2 it go in another sheet ..

Click to expand...

We don't know where in the sheet you want to enter the value. I have assumed cell A1. Change the code to suit.

needhelp2 said:

Answer for 2 would be like (3)3",(1)4",(2)1"
Answer for 3 would be like (2)2",(3)1",

Click to expand...

But bwe don't know where you want this answer put. I have assumed in the cell immediately under the InputCell. Based on my earlier assumption this would put it in cell A2.


Test this in a copy of your workbook.

To implement ..

1. Right click the sheet name tab (The sheet where you want to enter the number of interest) and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window.

4. Try entering a value in cell A1.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, Val <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, tmp <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, Counter <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> InputCell <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "A1" <SPAN style="color:#007F00">'<- Change to suit</SPAN><br>    <SPAN style="color:#00007F">Const</SPAN> sData <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "Data" <SPAN style="color:#007F00">'<- Name of sheet with data</SPAN><br>    <br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, Range(InputCell)) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Val = Range(InputCell).Value<br>        <SPAN style="color:#00007F">If</SPAN> Len(Val) > 0 <SPAN style="color:#00007F">Then</SPAN><br>            Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>            <SPAN style="color:#00007F">With</SPAN> Sheets(sData)<br>                <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>                c = .Rows(1).Find(What:=Val, LookIn:=xlValues, _<br>                    LookAt:=xlWhole, SearchFormat:=False).Column<br>                <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>                <SPAN style="color:#00007F">If</SPAN> c = 0 <SPAN style="color:#00007F">Then</SPAN><br>                    MsgBox Val & " not found"<br>                <SPAN style="color:#00007F">Else</SPAN><br>                    <SPAN style="color:#00007F">With</SPAN> .Columns(c)<br>                        r = 1<br>                        <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> .Cells(r).Value <> ""<br>                            <SPAN style="color:#00007F">If</SPAN> r = 1 <SPAN style="color:#00007F">Then</SPAN><br>                                tmp = .Cells(r + 1).Value<br>                                Counter = 1<br>                            <SPAN style="color:#00007F">ElseIf</SPAN> .Cells(r + 1).Value = .Cells(r).Value <SPAN style="color:#00007F">Then</SPAN><br>                                Counter = Counter + 1<br>                            <SPAN style="color:#00007F">Else</SPAN><br>                                s = s & ",(" & Counter & ")" & tmp & """"<br>                                Counter = 1<br>                                tmp = .Cells(r + 1).Value<br>                            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>                            r = r + 1<br>                        <SPAN style="color:#00007F">Loop</SPAN><br>                        s = Replace(s, ",", "", 1, 1)<br>                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>            Range(InputCell).Offset(1).Value = s<br>            Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>            Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

No its not working?
Is there any way on which i could share file to you?

needhelp2 said:

No its not working?

Click to expand...

That doesn't give me much to go on.

More information would help. For example

1. Did you test it on the same data as the sample provided or other data. If other data then try it for a start on the sample data.

Your sample data doesn't say what sheet name it is on or what rows/columns the data is in. I already mentioned that I used a sheet name called 'Data' (did you edit the code to your sheet name?) but I also assumed that the data started in cell A1.

2. Did it produce an error? If so what error message and on what line of code?

3. Did it produce the wrong result? If so,
- what was the value you typed in cell A1 of the other sheet? (Did you use cell A1 on the other sheet or a different InputCell?)
- what result did the code produce?
- what result should it have produced?

4. Did it do nothing at all?

5. Can you confirm that you implemented the code as described? In particular that you started by right-clicking the sheet name tab of the 'other' sheet, not the one with the data table?

6. BTW, what is the name of your sheet with the data table and what is the other sheet name where you want the results?

needhelp2 said:

Is there any way on which i could share file to you?

Click to expand...

That would be a 'last resort'. This is a public forum so as much as possible should be there for all to see.

You can post small screen shots directly in your posts. My signatuure block has 3 methods for doing that.