Need assistance on Macro (splitting the data)

[Pawan Sai]

Hey there,

Attached is the sample data for reference.

The workbook contains 2 sheets i.e., Raw data and Allocation sheet.
This is a chargeback allocation that I need to allocate to the team. My query is that... There is a column called "site" (Column L). That needs to be split according to the number I enter in the "Allocation" sheet. For instance, if the total data contains 400 rows and I need a macro where data should split based on the "site" column i.e., HYD 100 and BLR 200. So, whatever number I enter in the "Allocation" sheet should break accordingly, and the remaining data can be deleted.

Please review and do needful. I really appreciate any help you can provide.

Sample file.xlsx

[PCI]

the remaining data can be deleted.

It means that the LAST rows out of 100 for HYD and the LAST rows out of 200 for BLR will be deleted?

[sintek]

Assumes both sites exist...As per sample file requirement...Keep 63 BLR and keep 64 HYD if that much exists...If not...leave as is
Change red snippet to Delete after testing to see if code works...

Please Login or Register  to view this content.

[Pawan Sai]

Hi PCI,
It means if the data is 300 and I need only 200 i.e., BLR 100 and HYD 100 so remaining 100 can be deleted. So, basically whatever the number I enter should only display in Raw data.

[Pawan Sai]

Hey Sintek,

Thanks for looking into the query.
I guess the code was not working fine. It was highlighting with yellow some data but it's not splitting the data according to the number I enter in sheet 2 ("Allocation"). Can you please review it and advise with the new/modified code?

[beyond Excel]

Hello. I ask you:

a) You already commented on the number of rows to delete but: And what rows should be deleted? What is the elimination criteria?...

b) It seems to me that the best thing would be for you to take the workbook from Post #1 and add a third sheet in which you show us the expected final result...

[sintek]

I guess the code was not working fine. It was highlighting with yellow some data but it's not splitting the data according to the number I enter in sheet 2 ("Allocation").

I purposely did that and explained above why...The yellow highlighted is the data that will be deleted keeping only the required allotted lines...
I wanted you to first see if this is what you were wanting...If so, you had to change the highlight code snippet to delete, which will leave only the allotted lines remaining...
Change this...

Please Login or Register  to view this content.

to...

Please Login or Register  to view this content.

By doing this...Only the allotted lines that you stipulated would remain...The rest would be deleted...
If this is not what you wanted, then I suggest, as per beyond Excel, uploading a sample file showing a before | after expected scenario so that we can see exactly what it is you would want to achieve...

[bebo021999]

See below code.

Option Explicit 
Sub allocate() 
Dim lr&, i&, cell As Range, BLRcount&, HYDcount&, site 
With Sheets("Allocation") 
    BLRcount = .Range("C4") 
    HYDcount = .Range("D4") 
End With 
With Sheets("Raw Data") 
    lr = .Cells(Rows.Count, "L").End(xlUp).Row ' last row with data in column L 
    site = .Range("L2:L" & lr).Value ' store column L value into array 
    For i = 1 To UBound(site) ' loop thru column L value, and subtract from total till 0, then delete value 
        Select Case site(i, 1) 
            Case Is = "BLR" 
                If BLRcount = 0 Then 
                    site(i, 1) = "" 
                Else 
                    BLRcount = BLRcount - 1 
                End If 
            Case Is = "HYD" 
                If HYDcount = 0 Then 
                    site(i, 1) = "" 
                Else 
                    HYDcount = HYDcount - 1 
                End If 
        End Select 
    Next 
    .Range("L2:L" & lr).Value = site ' copy back site into sheet 
    .Range("L2:L" & lr).SpecialCells(xlBlanks).EntireRow.Delete ' delete blank cells in bulk 
End With 
End Sub  

[jindon]

I need a macro where data should split based on the "site" column i.e., HYD 100 and BLR 200. So, whatever number I enter in the "Allocation" sheet should break accordingly, and the remaining data can be deleted.

You didn't tell us about remaining data for other than HYD/BLR if there are any.
Simply

Please Login or Register  to view this content.

[Pawan Sai]

Hey Sintek,

It's my bad...I was not able to understand that before. Very sorry for that. Yea the macro was working fine now. I replaced the code "If X2 - X1 + 1 > Num Then .Rows(X1 + Num & ":" & X2).Interior.Color = vbYellow" with "If X2 - X1 + 1 > Num Then .Rows(X1 + Num & ":" & X2).Delete". This was too perfectly working and meeting all my requirements. Thanks a lot for your quick assistance.

[Pawan Sai]

Hey bebo021999,

Thanks a lot for looking into the query and this code was perfect. Correctly working on the data I enter without any churns. Thank you once again for your assistance.

[Pawan Sai]

Hey Jindon,

This code meets my requirements and working fine. Thanks a lot for your assistance on the query I posted.

[Pawan Sai]

Hey beyond excel,

Thanks for looking into the query. No, there is no need to have the deleted data. So requested the macro to eliminate the data apart from the number I entered in the table. However, the query got resolved from the above codes mentioned. Thanks for the time for looking into the query.

[Pawan Sai]

Thank You all for your great help. All the codes mentioned by you were working fine.
Surprisingly, I found many people here to help people with their queries...

Thanks a lot for everyone for your great assistance on queries.

[Pawan Sai]

Resolving the thread as the query was resolved.

Thank you all once again.

[sintek]

Hey Sintek,

It's my bad...I was not able to understand that before. This was too perfectly working and meeting all my requirements. Thanks a lot for your quick assistance.

Glad you got it sorted...Tx for rep...Happy Coding...