copy data from one column to another on certain condition

[cds]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]batsman[/TD]
[TD] [/TD]
[TD]runs[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD] sunil[/TD]
[TD][/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD] c kapil b salil[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD] malcom [/TD]
[TD][/TD]
[TD]28[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]runout[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]peter [/TD]
[TD][/TD]
[TD]49[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]b steeve [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]sachin [/TD]
[TD][/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]c lily b steev [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]extra[/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]total[/TD]
[TD][/TD]
[TD]130[/TD]
[/TR]
</tbody>[/TABLE]

Hi, I have data something like aboves

I want help to write vba for copying data in between batsman to extra. Here I want to copy a3 to b2, a5 to b4 & so on whenever c column is blank between batsman & extra . Please help me out


thanks in advance

 

[cds]

[TABLE="width: 341"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD]Here is my data. I


1 SRH vs RCB, 2 Hyderabad[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"] 3 Sunrisers Hyderabad Innings - 4 207/4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"] 5 Royal Challengers Bangalore 6 Innings - 172[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] 7 Batsmen[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] 8 Batting[/TD]
[TD]Runs[/TD]
[TD]B[/TD]
[TD]4s[/TD]
[/TR]
[TR]
[TD] David Warner[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="colspan: 2"]c Mandeep b Aniket Choudhary[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Shikhar Dhawan[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]c Sachin Baby b Binny[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Moises Henriques[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]c Sachin Baby b Chahal[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Yuvraj Singh[/TD]
[TD="align: right"]62[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]b Tymal Mills[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Deepak Hooda[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]not out[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ben Cutting[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]not out[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Extras 7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"](b - 0, w - 5, nb - 1, lb - 1)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total : 207[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"](20 Ovrs 4 Wkts) RR 10.35[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bowlers[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bowler[/TD]
[TD]O[/TD]
[TD]M[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]Tymal Mills[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]31[/TD]
[/TR]
[TR]
[TD]Aniket Choudhary[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]55[/TD]
[/TR]
[TR]
[TD]Yuzvendra Chahal[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]Sreenath Aravind[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]36[/TD]
[/TR]
[TR]
[TD]Shane Watson[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]41[/TD]
[/TR]
[TR]
[TD]Travis Head[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD]Stuart Binny[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]FOW[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]1-19 (David Warner - 1.4 ovr), 2-93 (Shikhar Dhawan - 10.3 ovr), 3-151 (Moises Henriques - 15.2 ovr), 4-190 (Yuvraj Singh - 18.4 ovr)



Row 8 is heading sort of data. I want data from 9th onwards upto "extra"[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Ok, how about

Sub Batsmen()
   With Range("C8:C" & Columns(1).find(What:="Extras", LookAt:=xlPart, MatchCase:=False).Row - 1)
   .SpecialCells(xlBlanks).Offset(-1, -1).FormulaR1C1 = "=r[1]c[-1]"
   End With
   With Intersect(ActiveSheet.UsedRange, Range("B:B"))
      .Value = .Value
'      .SpecialCells(xlConstants).Offset(1, -1).Clear
'      .SpecialCells(xlConstants).Offset(1, -1).EntireRow.Delete
   End With
End Sub

 

[Peter_SSs]

@cds
When I 'Reply With Quote' to post #11 and look at your table in WYSIWYG mode,
- 'Runs' is in the second column. If that is the case, then where are we to put the dismissal information? Do we need to create a new column?
- Extras 7 is all in one cell in the first column. Is that actually what you have?

BTW, If there were no Extras in the innings, does the 'Extras' line still always appear in your scorecard?

Since both Fluff & I seem to be somewhat unsure of your exact layout, could you use the link in my signature block below to get one of the HTML Makers and post an accurate screen shot of your sheet? It only needs to go from the first row down to, say, the 'Total: 207' row

Here is what I get when I paste your data into my sheet.

Excel Workbook
	A	B	C	D
1	1 SRH vs RCB, 2 Hyderabad
2	3 Sunrisers Hyderabad Innings - 4 207/4
3	5 Royal Challengers Bangalore 6 Innings - 172
4	7 Batsmen
5	8 Batting	Runs	B	4s
6	David Warner	14	8	2
7	c Mandeep b Aniket Choudhary
8	Shikhar Dhawan	40	31	5
9	c Sachin Baby b Binny
10	Moises Henriques	52	37	3
11	c Sachin Baby b Chahal
12	Yuvraj Singh	62	27	7
13	b Tymal Mills
14	Deepak Hooda	16	12	0
15	not out
16	Ben Cutting	16	6	0
17	not out
18	Extras 7
19	(b - 0, w - 5, nb - 1, lb - 1)
20	Total : 207
Sample

 

[cds]

THanks sir, it is working. THank yo. I have One Question. Sometime my data may not start from row no.8, then how to run the query. Further, after running the above query aI want to delete all rows where c column is blank for the same range i.e from batting to extra only . Can you help me out.


Sir, Thank once again for spending your valuable time to helping me out.

 

[Peter_SSs]

Any chance you could address my questions so that I can continue my interest in the thread too?

 

[cds]

Any chance you could address my questions so that I can continue my interest in the thread too?

Yes sir sure, Thank for your time, I will be inserting column b to capture my data before running vba. Furthermore, I want to copy data between batting to extra to another page. For me vba given 'fluff" is working copy data . Now I want dynamically select data between batting & extra rather than c8, How do i do it. Please help me

 

[Peter_SSs]

Yes sir sure, Thank for your time, I will be inserting column b to capture my data before running vba.

That only answers one of my questions. What about the other two?

- Extras 7 is all in one cell in the first column. Is that actually what you have?

BTW, If there were no Extras in the innings, does the 'Extras' line still always appear in your scorecard?

Also ..

I want to copy data between batting to extra to another page

Does the other page already exist? If so,
- What is its name?
- Is there any data already in the 'other' sheet that needs to be kept, or can the code delete it all and start with a clean sheet?

 

[cds]

Peter_sss Sir thank you very much . Sorry I didn't see your question.
Extras row start with Extras followed by some numbers say here it is Extras 7 . I have Two sheets per match , first match names 1 & 2 , 3 & 4 for second match. After getting dismissal info i will club all data between batting to Extra to separate sheet with sheet name added in a column in the summary sheet for further analysis .

 

[Peter_SSs]

Peter_sss Sir thank you very much . Sorry I didn't see your question.
Extras row start with Extras followed by some numbers say here it is Extras 7 . I have Two sheets per match , first match names 1 & 2 , 3 & 4 for second match. After getting dismissal info i will club all data between batting to Extra to separate sheet with sheet name added in a column in the summary sheet for further analysis .

That didn't seem to exactly address my questions, but see if this is any use.
It assumes that original data is in the active sheet when the code is run. The code creates a new sheet and puts the results from Batting to Extras (including both those rows) onto that new sheet.

Sub ScoreCard_v3()
  Dim wsResults As Worksheet
  
  Application.ScreenUpdating = False
  Columns("B").Insert
  With Range("B" & Columns("A").Find(What:="Batting", LookAt:=xlPart, MatchCase:=False).Row + 1 & _
              ":B" & Columns("A").Find(What:="Extra", LookAt:=xlPart, MatchCase:=False).Row - 2)
    .Value = Evaluate(Replace(Replace("if(#="""","""",^)", "#", .Offset(0, 1).Address), "^", .Offset(1, -1).Address))
    .Offset(1, -1).Value = Evaluate(Replace(Replace("if(#="""","""",%)", "#", .Offset(1, 1).Address), "%", .Offset(1, -1).Address))
    .Offset(1, -1).SpecialCells(xlBlanks).EntireRow.Delete
    Sheets.Add After:=ActiveSheet
    Set wsResults = ActiveSheet
    .Offset(-1).Resize(.Rows.Count + 2).EntireRow.Copy Destination:=wsResults.Range("A1")
  End With
  wsResults.UsedRange.Columns.AutoFit
  Application.ScreenUpdating = True
End Sub

 

[cds]

Dear Peter_SSs , Sir thank you very much indeed it copies to new sheet . If I want to add similar data from multiple sheet to One sheet . How Do I do it, Please guide me. Also advise me any resources available to improve my excel skill.


Thank you once again