Comparing cell values and moving whole rows

comppi · Jul 2, 2014

I'm trying to compare the values of columns B and G and if any of the cells are G < B, I would like to move the whole row into the second worksheet. And only if possible, it would be awesome if the macro could check column A for duplicates before the value comparison and sum up the column B if there are duplicates. In my example row 6 and 7 on column A have the same suppliers code and the amount totals to 2.
In this case for example, the value of the third row of G is lower than B, and therefore I would like the whole row 3 to be cut out from worksheet 1 and moved to worksheet 2.

I've tried making my own macros from other examples, but haven't really found anything that would suite my needs and fulfill the objective. I would be so thankful for any help provided! I understand that this might seem really complicated since I'm not that good at phrasing my problem

.

Original view of sheet 1:

SUPPLIERSCODE	ORDERAMOUNT	PRODUCTNAME	BARCODE	SUPPLIERSCODE	WAREHOUSE STOCK
1234	2,00	TEST PRODUCT 1	9999999	1234	2,00
1235	4,00	TEST PRODUCT 2	9999999	1235	3,00
1236	1,00	TEST PRODUCT 3	9999999	1236	1,00
1237	1,00	TEST PRODUCT 4	9999999	1237	1,00
1238	1,00	TEST PRODUCT 5	9999999	1238	1,00
1238	1,00	TEST PRODUCT 5	9999999

The macro would first make the sheet look like:

SUPPLIERSCODE	ORDERAMOUNT	PRODUCTNAME	BARCODE	SUPPLIERSCODE	WAREHOUSE STOCK
1234	2,00	TEST PRODUCT 1	9999999	1234	2,00
1235	4,00	TEST PRODUCT 2	9999999	1235	3,00
1236	1,00	TEST PRODUCT 3	9999999	1236	1,00
1237	1,00	TEST PRODUCT 4	9999999	1237	1,00
1238	2,00	TEST PRODUCT 5	9999999	1238	1,00

And then cut out the rows in which G<B, so the sheet 1 would look like:

SUPPLIERSCODE	ORDERAMOUNT	PRODUCTNAME	BARCODE	SUPPLIERSCODE	WAREHOUSE STOCK
1234	2,00	TEST PRODUCT 1	9999999	1234	2,00
1236	1,00	TEST PRODUCT 3	9999999	1236	1,00
1237	1,00	TEST PRODUCT 4	9999999	1237	1,00

And Sheet 2:

1235	4,00	TEST PRODUCT 2	9999999		1235	3,00
1238	2,00	TEST PRODUCT 5	9999999		1238	1,00

saltkev · Jul 2, 2014

Hi

Try this under a command button

Code:

Application.ScreenUpdating = False

Static fx, Lr, LrRange, WS1, WS2, RowHolder
Set WS1 = Sheets("sheet1") ' Alter Sheetname if Required
Set WS2 = Sheets("sheet2") ' Alter Sheetname if Required
WS1.Activate
Lr = ActiveSheet.Range("a70000").End(xlUp).Row
      
LrRange = ActiveSheet.Range("a2:a" & Lr).Address(False, False)
 
 

For Each fx In ActiveSheet.Range(LrRange)
        If fx.Value > "" Then
        
        RowHolder = fx.Address(0, 0)
        
        If fx.Offset(0, 5).Value = "" Then
        
           fx.Offset(0, 5).Value = fx.Value
           fx.Offset(0, 6).Value = fx.Offset(0, 1).Value
        
        End If
        
            If fx.Offset(0, 1).Value = fx.Offset(0, 6).Value Then
            
                    'Do Nothing
            Else
            
                   fx.EntireRow.Cut
                   
                    
                    WS2.Activate
                    
                             If ActiveSheet.Range("a2").Value = "" Then
                             
                                ActiveSheet.Range("a2").Select
                                ActiveSheet.Paste
                                WS1.Activate
                                Range(RowHolder).Select
                                ActiveCell.EntireRow.Select
                                Selection.Delete Shift:=xlUp
                                GoTo ender
                                
                                
                             ElseIf ActiveSheet.Range("a2").Value > "" And ActiveSheet.Range("a3").Value = "" Then
                             
                                ActiveSheet.Range("a3").Select
                                ActiveSheet.Paste
                                WS1.Activate
                                Range(RowHolder).Select
                                ActiveCell.EntireRow.Select
                                Selection.Delete Shift:=xlUp
                                GoTo ender
                                                          
                                                          
                             ElseIf ActiveSheet.Range("a2").Value > "" And ActiveSheet.Range("a3").Value > "" Then
                             
                                ActiveSheet.Range("a2").Select
                                Selection.End(xlDown).Select
                                ActiveCell.Offset(1, 0).Select
                                ActiveSheet.Paste
                                WS1.Activate
                                Range(RowHolder).Select
                                ActiveCell.EntireRow.Select
                                Selection.Delete Shift:=xlUp
                                GoTo ender
                             End If
             End If
        End If
ender:
WS1.Activate

Next fx

Regards

Kev

comppi · Jul 2, 2014

You're so awesome. This is so close to what I wanted. Only 1 thing I would have liked different, but like I said, I'm not that good at explaining this in English.

The same product (the same suppliers code and product name) might be on the order side on 2 or more different rows like this:

SUPPLIERSCODE	ORDERAMOUNT	PRODUCTNAME	BARCODE	SUPPLIERSCODE	WAREHOUSE STOCK
11112	3,00	TEST1	6417901007009	8017738	1,00
11111	1,00	TEST2	6430104924201	11111	1,00
11111	1,00	TEST2	6430104924201

Now the macro copies A4 & B4 to F4 & G4. What I wanted that it would do was that it combines A3 B3 and A4 B4, and removes the remaining product name and row, so the end result would be the following:

SUPPLIERSCODE	ORDERAMOUNT	PRODUCTNAME	BARCODE	SUPPLIERSCODE	WAREHOUSE STOCK
11112	3,00	TEST1	6417901007009	8017738	1,00
11111	2,00	TEST2	6430104924201	11111	1,00

And after that, it will check the amounts of B and G like it now does.

comppi · Jul 2, 2014

****, I didnt have time to edit my second post. This is the fixed one:

You're so awesome. This is so close to what I wanted. Only 1 thing I would have liked different, but like I said, I'm not that good at explaining this in English.

The same product (the same suppliers code BUT DIFFERENT product name) might be on the order side on 2 or more different rows like this:

SUPPLIERSCODE	ORDERAMOUNT	PRODUCTNAME	BARCODE	SUPPLIERSCODE	WAREHOUSE STOCK
11111	1,00	TEST1	6417901007009	11111	1,00
11112	1,00	TEST2	6417901007011	11112	1,00
11112	1,00	TEST2OLD	6417901007012

Now the macro copies A4 & B4 to F4 & G4. What I wanted that it would do was that it combines A3 B3 and A4 B4, and removes row that has the old product name and barcode, so the end result would be the following:

SUPPLIERSCODE	ORDERAMOUNT	PRODUCTNAME	BARCODE	SUPPLIERSCODE	WAREHOUSE STOCK
11111	1,00	TEST1	6417901007009	11111	1,00
11112	2,00	TEST2	6417901007011	11112	1,00

And after that, it will check the amounts of B and G like it now does.

saltkev · Jul 2, 2014

Hi

Will the SupplierCode Always be in order or will we need to order thew data first. Also will the second occurance of Supplier code always have cells "F & G" blank.

Kev

comppi · Jul 2, 2014

Actually first I have to sort columns ABCD so that first occurrence of supplier code will be ascending from low to high. I will add the second supplier code and warehouse stock afterwards, so they are not even present when the combining happens. I actually think It might be easier to just have 2 buttons then. First button when I load the ordered items into excel, it will sort them, and combine duplicate suppliercodes. Then the second button that I push when I have added the second supplier code and warehouse stock, which are already in ascending order via supplier code.

saltkev · Jul 2, 2014

OK

leave it with me

saltkev · Jul 2, 2014

Hi

Try this as button 1. is it doing what you want? as button 1. I have a problem and I think it is related to your keyboard layout, my layout doesn't recognise your numbers as numbers because of the commas, but it may work OK for you we will have to see.

Code:

Application.ScreenUpdating = False

Static fx, Lr, LrRange, WS1, Lr1, Lr1Range, counter

Set WS1 = Sheets("sheet1") ' Alter Sheetname if Required
WS1.Activate
Lr = ActiveSheet.Range("a70000").End(xlUp).Row
      
LrRange = ActiveSheet.Range("a2:g" & Lr).Address(False, False)
Lr1 = ActiveSheet.Range("a70000").End(xlUp).Row
      
Lr1Range = ActiveSheet.Range("a2:A" & Lr1).Address(False, False)
Range(LrRange).Select
    WS1.Sort.SortFields.Clear
    WS1.Sort.SortFields.Add Key:=Range(Lr1Range) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With WS1.Sort
        .SetRange Range(LrRange)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    
 counter = 0
While counter < 2
    counter = counter + 1    ' Increment Counter.
    
    
    
            For Each fx In ActiveSheet.Range(Lr1Range)
                     If fx.Value > "" Then
        
               
                            If fx.Value = fx.Offset(1, 0).Value Then
        
                                fx.Offset(0, 1).Value = (fx.Offset(0, 1).Value + fx.Offset(1, 1).Value)
                                fx.Offset(1, 0).Select
                                Selection.EntireRow.Select
                                Selection.Delete Shift:=xlUp
                            End If
              
                    End If
              
            Next fx
   
Wend

regards

Kev

comppi · Jul 3, 2014

The first button works flawlessly, but I have noticed that the second button has some kind of a bug. Don't know if it's related to the number formatting or something, but sometimes it decides to move everything to the second sheet, even when order and supply are identical and warehouse stocks are above the order amount. Actually it has to be related to formatting. Excel recognizes the data I pull out from the warehouse db as text, even though they are purely numbers. Don't know how I can change that. The cells are preformatted to be only integers.. And when it does start moving everything, it even moves the headers to the other sheet! Mystifying

saltkev · Jul 3, 2014

No. The first button is the first button, the second I have to send you, the idea was to get your approval on the first stage before I moved onto the second. Correct me if I am wrong but the second stage should. Look at the order quantity and the stock holding quantity, if there is a differance, remove that complete row to sheet 2.

Please comment.

regards

kev

Comparing cell values and moving whole rows

comppi

New Member

Excel Facts

saltkev

Active Member

comppi

New Member

comppi

New Member

saltkev

Active Member

comppi

New Member

saltkev

Active Member

saltkev

Active Member

comppi

New Member

saltkev

Active Member

Similar threads

Forum statistics

Share this page

Comparing cell values and moving whole rows

New Member

Excel Facts

Active Member

New Member

New Member

Active Member

New Member

Active Member

Active Member

New Member

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock