Way to find out certain numebrs from a Cell and add it in serial number wise

[sudhansu121]

Hello Experts,
I am having tough time in copy pasting the numbers from an excel sheet cell to individual rows.

the following Numbers have duplicates with same datetime but different visitnums. extrt='data and id in ( 5006,5023,5030,5033,5037,5122).5023,5030,5033 were reported previously.

17-03-2021​

Alex

Not now

So what I do from the above data is,
I copy one by one the numbers in Bold (5006,5023 and so on) to one row each and then copy the Date, Name and status like below.

5006​	17-03-2021​	Alex	Not now	the following Numbers have duplicates with same datetime but different visitnums. extrt='data and id in were reported previously.
5023​	17-03-2021​	Alex	Not now	the following Numbers have duplicates with same datetime but different visitnums. extrt='data and id in were reported previously.

So is there a way to this automatically with help of VBA?

 

[Zot]

I was looking at this. Is this exactly what was written in a cell?
the following Numbers have duplicates with same datetime but different visitnums. extrt='data and id in ( 5006,5023,5030,5033,5037,5122).5023,5030,5033 were reported previously.

( 5006,5023,5030,5033,5037,5122).5023,5030,5033

I thought the duplicate numbers are these 3 numbers only 5023,5030,5033 ? It looks like you want to list down 5006,5023,5030,5033,5037,5122 . Is this correct?

 

[Zot]

Assuming:
1) You have multiple rows of data. Starting row =2
2) You want to list down number in bracket only
3) This is always the same the following Numbers have duplicates with same datetime but different visitnums. extrt='data and id in were reported previously.

Step:
1) Copy data to Sheet1. Run program
2) The result will be in Sheet2

Sub ListVertical()

Dim k&, m&, n&, StartPoint&, StopPoint&
Dim ArryNum$(), strNumber$, strDesc$
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = ActiveWorkbook.Sheets("Sheet1")
Set ws2 = ActiveWorkbook.Sheets("Sheet2")

For m = 2 To ws1.Range("A" & ws1.Rows.Count).End(xlUp).Row
    StartPoint = InStr(ws1.Range("A2"), "(") + 1
    StopPoint = InStr(ws1.Range("A2"), ")")
    strNumber = Trim(Mid(ws1.Range("A2"), StartPoint, StopPoint - StartPoint))
    ArryNum = Split(strNumber, ",")
    strDesc = "the following Numbers have duplicates with same datetime but different visitnums. extrt='data and id in were reported previously."  ' assuming this is always be the same
    
    n = ws2.Range("A" & ws2.Rows.Count).End(xlUp).Row
    For k = LBound(ArryNum) To UBound(ArryNum)
        n = n + 1
        With ws2
            .Range("A" & n) = ArryNum(k)
            .Range("B" & n) = ws1.Range("B" & m)
            .Range("C" & n) = ws1.Range("C" & m)
            .Range("D" & n) = ws1.Range("D" & m)
            .Range("E" & n) = strDesc
        End With
    Next k
Next m

End Sub

 

[sudhansu121]

I was looking at this. Is this exactly what was written in a cell?
the following Numbers have duplicates with same datetime but different visitnums. extrt='data and id in ( 5006,5023,5030,5033,5037,5122).5023,5030,5033 were reported previously.

( 5006,5023,5030,5033,5037,5122).5023,5030,5033

I thought the duplicate numbers are these 3 numbers only 5023,5030,5033 ? It looks like you want to list down 5006,5023,5030,5033,5037,5122 . Is this correct?

Hi Zot,
Thanks for looking into it.
I want to list down 5006,5023,5030,5033,5037,5122 like separate rows and the description is dynamic and could be changed..

the following Numbers have duplicates with same datetime but different visitnums. extrt='data and id in ( 5006,5023,5030,5033,5037,5122).5023,5030,5033 were reported previously.	17-03-2021​	Alex	Not now
5006​	17-03-2021​	Alex	Not now	the following Numbers have duplicates with same datetime but different visitnums. extrt='data and id in were reported previously.
5023​	17-03-2021​	Alex	Not now	the following Numbers have duplicates with same datetime but different visitnums. extrt='data and id in were reported previously.
RS25Apr2020: 1.) 5473, - Queried/ 2.) V10 & V11- 5415 5378 5373 5372 5355 5330 5310 5309 5308 5284 5280 5272 5269 5247 5224 5216 5212 5204 5203 5199 5193 5188 5174 5169 5165 5164 5162 5130 5064- NAR- Visit 10 is marked as NA/ 5356,5146,5147, 5134, 5120, 5110, 5034- Issue is in followup with CDL/3.)V15 & V16- 5223 5212 5202 5188 5185 5165 5164 5163 5161 5158 5143 5112 5109 5107 5105 5088 5074 5055 5035 5031 5026- NAR- Visit marked as Not Applicable/ 5134, 5115- Issue is in followup with CDL/ 4.) 5571- Resolved now 5.) 5486, 5430- Resolved now 6.)5402- Queried 7.) 5324, 5307, 5167, 5140- NAR- Visit marked as Not applicable 8.)5502- Queried 9.)5568- Resolved now 10.)5019- Queried
subjid 5473 share the same visit date for visitnum 1 and visitnum 2; subjid 5415 5378 5373 5372 5356 5355 5330 5310 5309 5308 5284 5280 5272 5269 5247 5224 5216 5212 5204 5203 5199 5193 5188 5174 5169 5165 5164 5162 5147 5146 5134 5130 5120 5110 5064 5034 share the same visit date for visitnum 10 and visitnum 11; subjid 5223 5212 5202 5188 5185 5165 5164 5163 5161 5158 5143 5134 5115 5112 5109 5107 5105 5088 5074 5055 5035 5031 5026 share the same visit date for visitnum 15 and visitnum 16; subjid 5571 share the same visit date for visitnum 2 and visitnum 3; subjid 5486 5430 share the same visit date for visitnum 5 and visitnum 6; subjid 5402 share the same visit date for visitnum 8 and visitnum 9; subjid 5324 5307 5167 5140 share the same visit date for visitnum 9 and visitnum 10; subjid 5502 the visit date for visitnum 2 is earlier than the visit date for visitnum 1; subjid 5568 the visit date for visitnum 4 is earlier than the visit date for visitnum 3; subjid 5019 the visit date for visitnum 14 is almost one year earlier than the visit date for visitnum 13 Xinhua commented on May20, 2020 after DM's action. Some issues reported above are removed. Per the action had taken and the current data set, the remaining issues for further actions are: 1) subjid 5324 5307 5167 5140 share the same visit date for visitnum 9 and visitnum 10; --- DM's action said "visit marked as Not applicable". Need to know which visitnum is marked as not applicable? 2) subjid 5019 the visit date for visitnum 14 is almost one year earlier than the visit date for visitnum 13 -- Queried. (Xinhua comments: visit13 is 23-Dec-2019, visit14 is 06-Jan-2019. Could it be possible the year of visit14 should be 2020 but wrongly typed as 2019?)

 

[sudhansu121]

Assuming:
1) You have multiple rows of data. Starting row =2
2) You want to list down number in bracket only
3) This is always the same the following Numbers have duplicates with same datetime but different visitnums. extrt='data and id in were reported previously.

Step:
1) Copy data to Sheet1. Run program
2) The result will be in Sheet2

Sub ListVertical()

Dim k&, m&, n&, StartPoint&, StopPoint&
Dim ArryNum$(), strNumber$, strDesc$
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = ActiveWorkbook.Sheets("Sheet1")
Set ws2 = ActiveWorkbook.Sheets("Sheet2")

For m = 2 To ws1.Range("A" & ws1.Rows.Count).End(xlUp).Row
    StartPoint = InStr(ws1.Range("A2"), "(") + 1
    StopPoint = InStr(ws1.Range("A2"), ")")
    strNumber = Trim(Mid(ws1.Range("A2"), StartPoint, StopPoint - StartPoint))
    ArryNum = Split(strNumber, ",")
    strDesc = "the following Numbers have duplicates with same datetime but different visitnums. extrt='data and id in were reported previously."  ' assuming this is always be the same
   
    n = ws2.Range("A" & ws2.Rows.Count).End(xlUp).Row
    For k = LBound(ArryNum) To UBound(ArryNum)
        n = n + 1
        With ws2
            .Range("A" & n) = ArryNum(k)
            .Range("B" & n) = ws1.Range("B" & m)
            .Range("C" & n) = ws1.Range("C" & m)
            .Range("D" & n) = ws1.Range("D" & m)
            .Range("E" & n) = strDesc
        End With
    Next k
Next m

End Sub

Hi Zot,
I tried this but its showing subscript out of range.

 

[Zot]

Now you have provided more data samples, it looks like the code will not work because it is no more in bracket like the 1st sample. I tried 1st sample and it worked just fine because it detect starting and closing bracket.

The later samples are not similar. I saw visitnum 1 visitnum2 but I have no idea where it refers to. I need explanation to find the pattern but it looks like it is not easy task. I see no pattern to extract which number.