Formula or Code

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 fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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
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.

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

Forum statistics

Threads
1,224,900
Messages
6,181,632
Members
453,059
Latest member
jkevin

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