automatically extract data from separate cells with variable rows inbetween when criteria met

mingandmong

Active Member
Joined
Oct 15, 2014
Messages
339
Hi
Im using excell 2016

i wish to exctract rows of certain cells of data into a new sheet when the two criteria are met in column "B"
my sheet has a 5000 rows and a just require a summary sheet

search column "B" 1st criteria = Name:(B:9) extract cell E:9 D:9 & C:9 and place in a new sheet A2:C2
Then search Column "B" 2nd criteria = Daily (B:18) extract cell C:20, E:20, G:20, I:20, K:20, M:20, O:20 & Q:20
and place in a new sheet D2:K2

repeat for the remaining sheet in a loop

i have place links below.. many thanks in advance

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]https://ibb.co/ssd6gw7
https://ibb.co/MR6qQH7


http:// [/FONT]
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
How about
Code:
Sub mingandmong()
   Dim Cl As Range
   Dim Ws As Worksheet
   
   Set Ws = Sheets("Sheet2")
   With Range("B1", Range("B" & Rows.Count).End(xlUp))
      .Replace "Name:", "=xxx", xlWhole, , False, , False, False
      With .SpecialCells(xlFormulas, xlErrors)
         .Offset(, 3).Copy Ws.Range("A2")
         .Offset(, 2).Copy Ws.Range("B2")
         .Offset(, 1).Copy Ws.Range("C2")
      End With
      .Replace "=xxx", "Name:", xlWhole, , False, , False, False
      .Replace "Daily", "=xxx", xlWhole, , False, , False, False
      With .SpecialCells(xlFormulas, xlErrors)
         Union(.Offset(2, 1), .Offset(2, 3), .Offset(2, 5), .Offset(2, 7), .Offset(2, 9), .Offset(2, 11), .Offset(2, 13), .Offset(2, 15)).Copy Ws.Range("D2")
      End With
      .Replace "=xxx", "Daily", xlWhole, , False, , False, False
   End With
End Sub
 
Upvote 0
Hi Fluff

i dont know how you do it, but im total impressed yet again with your expertise, and resolved first time around.. not the first time you have helped me out
i have noticed just a little querk with my data and has nothing to do with your formula/macro
the name(s) in coulmn "B1" end in a comma so my current dats is joe, and Elena, would be joe and Elena if that were possible and easy to add if not i can use another method to clean up

i have another issue for another sheet in the near future i hope your the one to resolve it for me.. thanx again for all your hard work
 
Upvote 0
Try adding this before the End Sub line
Code:
   Ws.Range("C:C").Replace ",", "", xlPart, , False, , False, False
I assume you mean col C not B, as that's where Joe & Elana end up.
 
Upvote 0
hi Fluff
i did mean "B" on sheet1 and yes "C" on sheet 2 .. you have resolved all my issues.. thankyou once again
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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