Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Dec 2009
    Location
    Down Under
    Posts
    50
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Best way to work with spreadsheets with "non-matching" rows ?

    I work with a number of spreadsheets in XLS that have stock or sales data. In order for me to operate in these spreadsheets I use copy and paste and of course this only works as long as I am very careful in keeping these sheets in synch. So each time I add an item in one sheet, I have to make sure I immediately add it in the other sheet as otherwise my next copy/paste will have all the information shifted. Some of the reports I get do not have output for Zero Values meaning the sheet will also be shorter than the master sheet. I need to be run this through a macro someone set up for me and this takes care of this issue. Sorry if my description is not very clear.

    I am sure there are clever ways to deal with managing information between sheets of different rows that addresses these issues. Can someone please point me to some helpfile or video tutorial or provide just the name of the function / procedure I could use, that would be very helpful.

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    9,209
    Thanks
    70
    Thanked 1,170 Times in 1,087 Posts
    Why don't you use an intermediate sheet to do the consolidation? Then you don't need to copy/paste or check sync.
    You would use references to the original sheets to collect the data and one sheet/template could be used for different data sheets.

    Are you able to post some sample data / a small sheet for us to work with?

    cheers, Paul
    Last edited by Paul T; 2019-03-16 at 00:58.

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    3,120
    Thanks
    170
    Thanked 841 Times in 766 Posts
    Beethoven,

    It is a little vague as to whether you are attempting to sync different workbooks or different sheets within one workbook.

    If Paul's method doesn't work for you, here is a backup method that might. Place the following code in the ThisWorkbook module:
    Code:
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    On Error Resume Next
    If Left(Application.CommandBars("Standard").Controls("&Undo").List(1), 5) = "Paste" Then
        For I = 1 To Worksheets.Count
            If Not Worksheets(I).Name = Sh.Name Then
                addr = Target.Address
                Application.EnableEvents = False
                Worksheets(I).Range(addr) = Target
                Application.EnableEvents = True
            End If
        Next I
    End If
    On error Goto 0
    End Sub
    When ever you paste onto a sheet, the Workbook_SheetChange event fires and looks at the Undo history. If the last action was a paste then it grabs the cell address that was just pasted to and adds the same value to the same cell on every other sheet keeping your sheets synced. This will only occur when you paste. It will handle paste and paste special.

    HTH,
    Maud

  4. #4
    Star Lounger
    Join Date
    Dec 2009
    Location
    Down Under
    Posts
    50
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Paul & Maud,
    thanks for your assistance. Sure, it is easier to see what I am talking about with example sheets. I have attached three sheets from different work books. All of them are seriously cut for size (the real stock sheet has 3750 rows and ends in column BQ), the sales sheet is maintained with the same number of rows so that I can cut/paste. The actual report that I am drawing (called Response )has only the number of rows that show actual sales (normally around 520 but I redacted this one leaving only some lines-)so all old/inactive product lines would show up in the stock listing for historical reasons but not in the response report. At the moment I can deal with this problem by running the sales report through a macro someone set up for me a long time ago that creates an output with the currently matching number of rows, allowing me to do the cut/paste. However if I wanted to work on an old sales report from 3 months ago and for some reason needed to integrate this into my current stock listing, I need to manually amend the data to shift down all those cells that had new stock items included since the old report (Whenever a new product is introduced the reports grow and cut/paste using reports from different dates becomes a problem.. I am also wondering if for some reason that macro function is embedded on a server stopped working as I would not even know how to access it to modify or migrate.

    The relevant sales figure is shown in Column C (Salesreport) and Column F (in the underlying response report) and reflected in Column AF (in the Stock Report). So what I am trying to achieve is to be able to easily have the data from ideally response report Column F transferred to the Stock Report Column AF, ideally straight from Response.
    Not sure if this makes it clearer but if you can provide some guidance, any help will be appreciated.
    Attached Files Attached Files

  5. #5
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    9,209
    Thanks
    70
    Thanked 1,170 Times in 1,087 Posts
    I still don't see what you are trying to do.
    Do you want to update the stock sheet from the sales sheet?

    cheers, Paul

  6. #6
    Star Lounger
    Join Date
    Dec 2009
    Location
    Down Under
    Posts
    50
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Paul,

    yes, consider my stock sheet as my main working sheet. This is being updated each week with the new inventory figures. I also run the sales report each week and indeed have to transfer the data from there (either sales or ideally response) into the stock sheet. This will then allow me to project stock
    consumption and reorders from suppliers.

  7. #7
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    9,209
    Thanks
    70
    Thanked 1,170 Times in 1,087 Posts
    How do you update the stock numbers? I can see 2 places but only one is used in the "Stock after delivery" column.
    We really need to understand your workflow to advise.

    cheers, Paul

  8. #8
    Star Lounger
    Join Date
    Dec 2009
    Location
    Down Under
    Posts
    50
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Paul,

    let's consider my stock sheet as given. I don't have any problems with getting the inventory data in there. The qty changes and is reflected in column I. (Column H is the previous week, and Column L is future stock given known purchase orders - neither column is relevant in this context). The issue is with the sales data that goes into column AF (at the moment) and that's where the reports having non matching number of rows (the sales report only showing data for items with sales) that I always have to be extremely careful. If I could get the result of the response report (column F) linked or transferred to the appropriate item field in colum AF of the stock sheet, that would be helpful. Please note that in preparing the example below I noticed that by redacting the original response sheet I made a mistake, so I attach a corrected version and also removed the empty rows- sorry.

    E.g. Item A1 shows a Net QTy (Sale) of 16 in Row F (cell F5) of Response - this data has to go into cell AF3 in the stock sheet.
    Item A30 shows a Net Qty (Sale) of 48 in cell F25 of Response - this is reflected in cell AF32
    so any data in Row F response should be linked to the relevant field in the stock sheet for the appropriate item currently AF (though with every new campaign that changes)
    Attached Files Attached Files

  9. #9
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    9,209
    Thanks
    70
    Thanked 1,170 Times in 1,087 Posts
    Assuming you need to create the response sheets and use the value from that to update stock.

    You can create the Response sheet with a standard filter.
    Create the Response sheet as an additional sheet in the Sales workbook.
    Select the data in Sales, create a filter where column C is greater than 0, select "copy to" and choose the new Response sheet.
    Note: Filters only work within a workbook, so you may need to copy/move the Response sheet to another workbook.

    Once the Response sheet exists you can use VLOOKUP to add the values to the stock sheet.
    Enter this in I2 and fill down. This uses the item number from the Stock sheet to lookup the sale value in the Response sheet.
    Code:
    =VLOOKUP(F2,'file:///D:/Temp/WS/Response Rev.xls'#$'BLA Response Catalogue Product'.A4:F30,4)
    cheers, Paul
    Last edited by Paul T; 2019-03-18 at 00:13.

  10. #10
    Star Lounger
    Join Date
    Dec 2009
    Location
    Down Under
    Posts
    50
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Paul,

    thank you but my explanation was not not clear with respect to the sequence. The response sheet is created via a simple download report I generate. So that data is there easily. I only use the sales sheet at the moment to process that data in a consistent way in the sales sheet for sales analysis and to cut/paste into the stock sheet. So the data should flow from Response either directly into stock or indirectly via sales into stock.

  11. #11
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    9,209
    Thanks
    70
    Thanked 1,170 Times in 1,087 Posts
    The VLOOKUP reflects the sales data into the stock sheet. Is that not what you wanted?

    cheers, Paul

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •