find all values corresponding to each values

D

Deepk

Board Regular
Joined
Mar 21, 2018
Messages
105
Office Version
  1. 2016
Platform
  1. Windows
hi all,

I want vba code for following activity. Sample data is provided below.

Copy each value in 'Find value' column and find all in column 'For Topic 1'. Paste the find all values in the column 'Topic 1'. Repeat this activity for all column Topic 1 to 4.

[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD]Find Value[/TD]
[TD]Topic 1[/TD]
[TD]Topic 2[/TD]
[TD]Topic 3[/TD]
[TD]Topic 4[/TD]
[TD]For Topic 1[/TD]
[TD]For Topic 2[/TD]
[TD]For Topic 3[/TD]
[TD]For Topic 4[/TD]
[/TR]
[TR]
[TD]TA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]TA|CA|PA|KA[/TD]
[TD]TA|CA|PA|KA[/TD]
[TD]CA|PA|KA[/TD]
[TD]PA|KA[/TD]
[/TR]
[TR]
[TD]PA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CA|PA|KA[/TD]
[TD]CA|PA|KA[/TD]
[TD]KA[/TD]
[TD]CA|PA|KA[/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]PA|KA[/TD]
[TD]CA|PA|KA[/TD]
[TD]PA|KA[/TD]
[TD]PA|KA[/TD]
[/TR]
[TR]
[TD]KA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]KA[/TD]
[TD]KA[/TD]
[TD]KA[/TD]
[TD]KA[/TD]
[/TR]
</tbody>[/TABLE]

The final data should appear like the table below.

Please help me with the code. Thank you.
[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD]Find Value[/TD]
[TD]Topic 1[/TD]
[TD]Topic 2[/TD]
[TD]Topic 3[/TD]
[TD]Topic 4[/TD]
[TD]For Topic 1[/TD]
[TD]For Topic 2[/TD]
[TD]For Topic 3[/TD]
[TD]For Topic 4[/TD]
[/TR]
[TR]
[TD]TA[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]TA|CA|PA|KA[/TD]
[TD]TA|CA|PA|TA[/TD]
[TD]CA|PA|KA[/TD]
[TD]PA|KA[/TD]
[/TR]
[TR]
[TD]PA[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]CA|PA|KA[/TD]
[TD]CA|PA|KA|TA[/TD]
[TD]KA[/TD]
[TD]CA|PA|KA[/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]PA|KA[/TD]
[TD]CA|PA|KA[/TD]
[TD]PA|KA[/TD]
[TD]PA|KA[/TD]
[/TR]
[TR]
[TD]KA[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]KA[/TD]
[TD]KA[/TD]
[TD]KA[/TD]
[TD]KA

[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
Try this:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG06Sep07
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, R [COLOR="Navy"]As[/COLOR] Range, Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]For[/COLOR] Ac = 1 To 4
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] Rng.Offset(, Ac)
            [COLOR="Navy"]If[/COLOR] InStr(R.Offset(, 4), Dn.Value) > 0 [COLOR="Navy"]Then[/COLOR] c = c + 1
        [COLOR="Navy"]Next[/COLOR] R
            Dn.Offset(, Ac).Value = c: c = 0
    [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
MickG said:
Try this:-
Code: 
[COLOR=Navy]Sub[/COLOR] MG06Sep07
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, R [COLOR=Navy]As[/COLOR] Range, Ac [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] c [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
    [COLOR=Navy]For[/COLOR] Ac = 1 To 4
        [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] R [COLOR=Navy]In[/COLOR] Rng.Offset(, Ac)
            [COLOR=Navy]If[/COLOR] InStr(R.Offset(, 4), Dn.Value) > 0 [COLOR=Navy]Then[/COLOR] c = c + 1
        [COLOR=Navy]Next[/COLOR] R
            Dn.Offset(, Ac).Value = c: c = 0
    [COLOR=Navy]Next[/COLOR] Ac
[COLOR=Navy]Next[/COLOR] Dn
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]
Regards Mick
Click to expand...

Hi Mick,

Thank you for your reply. I kept the first table in a worksheet at A1 and run the code. Nothing happened. Please help me.
 
Upvote 0
Try this:-

To Save and Run Code:-
Copy code from Thread
In Your Data sheet , Click "Alt+F11",:- Vb Window appears.
From the VBWindow toolbar, Click "Insert" ,"Module":- New VBwindow appears .
Paste Code into this window.
Close Vbwindow.


On sheet Click "Developer tab", Click "Macro". Macro dialog box appears.
Select Macro (with same name) from List.
On the right of Dialog box Click "Run"
The Sheet should now be updated.
Regrds Mick


NB:- When you run the code the worksheet with your data should be the Active sheet ( i.e. Open)
 
Upvote 0
MickG said:
Try this:-

To Save and Run Code:-
Copy code from Thread
In Your Data sheet , Click "Alt+F11",:- Vb Window appears.
From the VBWindow toolbar, Click "Insert" ,"Module":- New VBwindow appears .
Paste Code into this window.
Close Vbwindow.


On sheet Click "Developer tab", Click "Macro". Macro dialog box appears.
Select Macro (with same name) from List.
On the right of Dialog box Click "Run"
The Sheet should now be updated.
Regrds Mick


NB:- When you run the code the worksheet with your data should be the Active sheet ( i.e. Open)
Click to expand...

Hi Mick,

this code is working fine. Please forgive me if my requirement is not clear. I am writing this again.

B2 value should contain the count of cells in column 'For topic 1' containing value 'TA'.
B3 value should contain the count of cells in column 'For topic 1' containing value 'PA'.
C2 value should contain the count of cells in column 'For topic 2' containing value 'TA'.
C3 value should contain the count of cells in column 'For topic 2' containing value 'PA'.

And so on. I hope this is clear.
 
Upvote 0
You're welcome
If you think your results are not correct, please show your data again with your expected results.
NB:- Some of your original data does not seem quite correct !!!
 
Upvote 0
MickG said:
You're welcome
If you think your results are not correct, please show your data again with your expected results.
NB:- Some of your original data does not seem quite correct !!!
Click to expand...


Please run your code on the following table and bring this output.

[TABLE="class: cms_table_grid, width: 600"]
<tbody>[TR]
[TD]Find Value[/TD]
[TD]Topic 1[/TD]
[TD]Topic 2[/TD]
[TD]Topic 3[/TD]
[TD]Topic 4[/TD]
[TD]For Topic 1[/TD]
[TD]For Topic 2[/TD]
[TD]For Topic 3[/TD]
[TD]For Topic 4[/TD]
[/TR]
[TR]
[TD]TA[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]TA|CA|PA|KA[/TD]
[TD]TA|CA|PA|TA[/TD]
[TD]CA|PA|KA[/TD]
[TD]PA|KA[/TD]
[/TR]
[TR]
[TD]PA[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]CA|PA|KA|TA[/TD]
[TD]CA|PA|KA|TA[/TD]
[TD]KA[/TD]
[TD]CA|PA|KA[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]PA|KA[/TD]
[TD]CA|PA|KA[/TD]
[TD]TA[/TD]
[TD]PA|KA[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]KA|TA|PA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
Try this:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG06Sep49
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, R [COLOR="Navy"]As[/COLOR] Range, Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Rw = Cells(1).CurrentRegion.Rows.Count
[COLOR="Navy"]Set[/COLOR] Rng = Range("A2").Resize(Rw - 1)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
  [COLOR="Navy"]If[/COLOR] Not IsEmpty(Dn.Value) [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]For[/COLOR] Ac = 1 To 4
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] Rng.Offset(, Ac)
            [COLOR="Navy"]If[/COLOR] InStr(R.Offset(, 4), Dn.Value) > 0 [COLOR="Navy"]Then[/COLOR] c = c + 1
        [COLOR="Navy"]Next[/COLOR] R
            Dn.Offset(, Ac).Value = c: c = 0
    [COLOR="Navy"]Next[/COLOR] Ac
 [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
MickG said:
Try this:-
Code: 
[COLOR=Navy]Sub[/COLOR] MG06Sep49
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, R [COLOR=Navy]As[/COLOR] Range, Ac [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] c [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] Rw [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
Rw = Cells(1).CurrentRegion.Rows.Count
[COLOR=Navy]Set[/COLOR] Rng = Range("A2").Resize(Rw - 1)
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
  [COLOR=Navy]If[/COLOR] Not IsEmpty(Dn.Value) [COLOR=Navy]Then[/COLOR]
    [COLOR=Navy]For[/COLOR] Ac = 1 To 4
        [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] R [COLOR=Navy]In[/COLOR] Rng.Offset(, Ac)
            [COLOR=Navy]If[/COLOR] InStr(R.Offset(, 4), Dn.Value) > 0 [COLOR=Navy]Then[/COLOR] c = c + 1
        [COLOR=Navy]Next[/COLOR] R
            Dn.Offset(, Ac).Value = c: c = 0
    [COLOR=Navy]Next[/COLOR] Ac
 [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR] Dn
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]
Regards Mick
Click to expand...

Thank you very much.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Anigito
  • Question Question
Get Webpage Title Text by URL Generated in MS Excel
Replies
1
Views
1K
Anigito
Anigito
A
  • Question Question
code to copy last row & exclude zero value of the data in last row for each name
Replies
0
Views
291
Abdo
A
B
  • Question Question
Sort/Filter based on SUMIF Value
2
Replies
13
Views
990
Fluff
Fluff
A
  • Question Question
Delete most of all of ranges except specific ranges based on cell value
Replies
0
Views
201
Abdo
A
S
  • Question Question
Conditional top 10 Modes
Replies
1
Views
197
smit3446
S

Forum statistics

Threads
1,223,893
Messages
6,175,239
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

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