Formula or Code

V

Vishaal

Well-known Member
Joined
Mar 16, 2019
Messages
543
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
  2. Web
Hi All,

Thanks in advance

We have the following sheet

Excel 2010 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][th]
F​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td][/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td][/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td][/td][td=bgcolor:#D7DBDF]
S.No.​
[/td][td=bgcolor:#D7DBDF]
Year​
[/td][td=bgcolor:#D7DBDF]
​
[/td][td=bgcolor:#D7DBDF]
Batch​
[/td][td=bgcolor:#D7DBDF]
Sams​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td][/td][td]
1​
[/td][td]
9319​
[/td][td]
​
[/td][td]
3000​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td][/td][td]
2​
[/td][td]
3219​
[/td][td]
​
[/td][td]
3200​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td][/td][td]
3​
[/td][td]
7819​
[/td][td]
3000,3200,8000​
[/td][td]
8000​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td][/td][td]
4​
[/td][td]
2419​
[/td][td]
​
[/td][td]
4000​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td][/td][td]
5​
[/td][td]
1219​
[/td][td]
​
[/td][td]
2000​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td][/td][td]
6​
[/td][td]
9319​
[/td][td]
​
[/td][td]
3000​
[/td][td]
Sams​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td][/td][td]
7​
[/td][td]
6819​
[/td][td]
​
[/td][td]
8000​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td][/td][td]
8​
[/td][td]
9419​
[/td][td]
​
[/td][td]
4000​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12​
[/td][td][/td][td]
9​
[/td][td]
2419​
[/td][td]
​
[/td][td]
4000​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13​
[/td][td][/td][td]
10​
[/td][td]
8319​
[/td][td]
​
[/td][td]
3000​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
14​
[/td][td][/td][td]
11​
[/td][td]
3519​
[/td][td]
​
[/td][td]
5000​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
15​
[/td][td][/td][td]
12​
[/td][td]
5619​
[/td][td]
​
[/td][td]
6000​
[/td][td]
​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

and this is my result sheet

Excel 2010 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][th]
F​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
20​
[/td][td][/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
21​
[/td][td][/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
22​
[/td][td][/td][td=bgcolor:#D7DBDF]
S.No.​
[/td][td=bgcolor:#D7DBDF]
Year​
[/td][td=bgcolor:#D7DBDF]
​
[/td][td=bgcolor:#D7DBDF]
Batch​
[/td][td=bgcolor:#D7DBDF]
Flag​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
23​
[/td][td][/td][td]
1​
[/td][td]
9319​
[/td][td]
​
[/td][td]
3000​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
24​
[/td][td][/td][td]
2​
[/td][td]
3219​
[/td][td]
​
[/td][td]
3200​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
25​
[/td][td][/td][td]
3​
[/td][td]
7819​
[/td][td]
3000,3200,8000​
[/td][td]
8000​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
26​
[/td][td][/td][td]
4​
[/td][td]
2419​
[/td][td]
​
[/td][td]
4000​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
27​
[/td][td][/td][td]
5​
[/td][td]
1219​
[/td][td]
​
[/td][td]
2000​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
28​
[/td][td][/td][td]
6​
[/td][td]
9319​
[/td][td]
3000,2000,4000​
[/td][td]
3000​
[/td][td]
Sams​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
29​
[/td][td][/td][td]
7​
[/td][td]
6819​
[/td][td]
​
[/td][td]
8000​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
30​
[/td][td][/td][td]
8​
[/td][td]
9419​
[/td][td]
4000​
[/td][td]
4000​
[/td][td]
Sams​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
31​
[/td][td][/td][td]
9​
[/td][td]
2419​
[/td][td]
3000,8000,4000​
[/td][td]
4000​
[/td][td]
Sams​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
32​
[/td][td][/td][td]
10​
[/td][td]
8319​
[/td][td]
3000,8000,4000​
[/td][td]
3000​
[/td][td]
Sams​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
33​
[/td][td][/td][td]
11​
[/td][td]
3519​
[/td][td]
​
[/td][td]
5000​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
34​
[/td][td][/td][td]
12​
[/td][td]
5619​
[/td][td]
​
[/td][td]
6000​
[/td][td]
​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

We want we will set from where it will start i mean will define the row from where formula will start

We want a Formula/Code who can check that if "Sams" are in column F, then it will take the above three values and paste as text on Column D, but we don't want any duplicate entry if there is any duplicate entry then it will add one more number from above

we hope above the sheet clear the points

help pls with code/formula
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
Pls provide VBA/Formula/Code

in my post VBA was not so that we have posted it again
 
Upvote 0
Your example is complex, I reviewed it and did not understand.
You could explain step by step how you get from the source table to the destination table.
 
Upvote 0
DanteAmor said:
Your example is complex, I reviewed it and did not understand.
You could explain step by step how you get from the source table to the destination table.
Click to expand...

In my sheet when the formula find "Sams" in column F, in sheet F28 we have find "Sams" so the formula have added E28,27 and 26 value in D28

Again we have find "Sams" in column F, in sheet F30 we have find "Sams" so the formula have added E30,29 and 28 value in D30 (Sorry we forget to add other value in result sheet)

same process will follow till the "Sams" will find and we will not used same value from column E, for example D32
 
Upvote 0
Hi, try this:

The data must be in the cells as shown below.
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:25.66px;" /><col style="width:38.02px;" /><col style="width:33.27px;" /><col style="width:127.37px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td >S.No.</td><td >Year</td><td > </td><td >Batch</td><td >Sams</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td style="text-align:right; ">1</td><td style="text-align:right; ">9319</td><td > </td><td style="text-align:right; ">3000</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td style="text-align:right; ">2</td><td style="text-align:right; ">3219</td><td > </td><td style="text-align:right; ">3200</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td style="text-align:right; ">3</td><td style="text-align:right; ">7819</td><td style="text-align:right; ">3000,3200,8000</td><td style="text-align:right; ">8000</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td style="text-align:right; ">4</td><td style="text-align:right; ">2419</td><td > </td><td style="text-align:right; ">4000</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td style="text-align:right; ">5</td><td style="text-align:right; ">1219</td><td > </td><td style="text-align:right; ">2000</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td style="text-align:right; ">6</td><td style="text-align:right; ">9319</td><td style="text-align:right; ">3000,2000,4000</td><td style="text-align:right; ">3000</td><td >Sams</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td style="text-align:right; ">7</td><td style="text-align:right; ">6819</td><td > </td><td style="text-align:right; ">8000</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td style="text-align:right; ">8</td><td style="text-align:right; ">9419</td><td style="text-align:right; ">4000,8000,3000</td><td style="text-align:right; ">4000</td><td >Sams</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td><td style="text-align:right; ">9</td><td style="text-align:right; ">2419</td><td style="text-align:right; ">4000,8000,3000</td><td style="text-align:right; ">4000</td><td >Sams</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td > </td><td style="text-align:right; ">10</td><td style="text-align:right; ">8319</td><td style="text-align:right; ">3000,4000,8000</td><td style="text-align:right; ">3000</td><td >Sams</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td > </td><td style="text-align:right; ">11</td><td style="text-align:right; ">3519</td><td > </td><td style="text-align:right; ">5000</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td > </td><td style="text-align:right; ">12</td><td style="text-align:right; ">5619</td><td > </td><td style="text-align:right; ">6000</td><td > </td></tr></table>

Code: 
Sub find_flag()
  Dim j As Long, n As Long, c As Range, s As String
  For Each c In Range("E4", Range("E" & Rows.Count).End(xlUp))
    s = ""
    If c.Offset(, 1) = "Sams" Then
      n = 0
      For j = c.Row To 4 Step -1
        If InStr(1, s, Cells(j, "E")) = 0 Then
          n = n + 1
          If n > 3 Then Exit For
          s = s & Cells(j, "E") & ","
        End If
      Next
    End If
    If s <> "" Then c.Offset(, -1) = "'" & Left(s, Len(s) - 1)
  Next
End Sub
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Event code to populate message upon workbook open at specified dates
Replies
2
Views
206
bh24524
B
V
  • Question Question
copy from source sheet1 to destination sheet2 vba code
Replies
1
Views
250
Vishaal
V
F
  • Question Question
VBA, Selection with xlToRight but want to end at column JK
Replies
3
Views
143
Joe4
Joe4
D
  • Question Question
Can time frames/windows be used as a condition for any functions?
Replies
4
Views
98
dallas2699
D
A
  • Question Question
VBA Code for Email_For
Replies
8
Views
416
AbhishekJain
A

Forum statistics

Threads
1,223,952
Messages
6,175,596
Members
452,658
Latest member
GStorm

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