Welcome Guest   ·   Login   ·   Register   ·   Member List
Forum Home  >  Sudhian  >  General Software  >  Thread
Search  
  
  Advanced Search

   
 
Combining excel Spreadsheets
 
hugh
Posted: 23 February 2010 12:13 PM  
I’m Naboo, That’s Who!
Total Posts:  4303
Joined 

So at work we have some till software, which was written by monkeys.

the short story is that there are two spreadsheets/databases that i need to pull off the tills each month, one being a spread of all items sold, quanitites etc etc. the other is a simple PLU list with the PLUS and it’s price and it’s description, nowt else.

now the spreaddsheet with all the actual figures i’m after, lists each product in nothing but it’s PLU such as 16078, but no description, wheras the other spread will give you 16078 and it’s description but no sales figures.

i know it is possible, but now how, i would like to combine both sheets each month. i’m not even sure how to word this let alone do the thing.

basically both sheets would have the same list of PLU’s, but one has what the other doesn’t and vice verca, so i’d like excel or something to take both sets of PLU;s and then shift say the prduct description into a sheet cotaining the sales figueres as well.

bahhhhh, i’m dog tired, that reads like a piece of s****, sorry :(

but does anyone know what i’m trying to do? i’d love some help!

Profile
 
 
Sparbag
Posted: 27 February 2010 01:22 PM   [ # 1 ]  
Ace
Total Posts:  1104
Joined 

Hello Hugh,

I understand your frustration and I can state from experience that blaming the guys who made the documents will be blame misplaced. I have personal experience of the boss demanding that files be set in a specific way, until six months later demanding them be set in a competely different way, and wanting that completed today.

I am going to make several assumptions based on the two seperate Excel files and I am going to require you to either agree or disagree with them.

1. Each file is stored on the same computer.
2. the location of each file is within the same folder.
3. The data within the spreadsheet, has headers. This is to say that when looking at row 1 there are descriptions on the data below.

The easiest method is to pull the sheet from one file into the other. This will allow you to maintain all data in a single file. The longterm solution should be to merge the data into a single readable file format. Excel can do this with VBa but the data must be analysed for where the matches are to be made.
It is a simple procedure to write 1 column of an Excel file into 1 column of another. It requires analysis of the data to write the 1 column of the first Excel file into the corresponding rows of the second Excel file.

I’ll set this thread to notify me by email but do not hesitate in going to vbforums where I am also a member, but concentrate on programming issues. I can only apologise to the forum administrators if directing forum members to a different forum is unacceptable behaviour.

Kind regards

Steve

Profile
 
 
hugh
Posted: 28 February 2010 09:52 AM   [ # 2 ]  
I’m Naboo, That’s Who!
Total Posts:  4303
Joined 

i can agree with all three statements. i am in no way in need of a massively automated system, it’s kinda a once a month need in a fairly small business.

i do indeed need to take one column out of spreadsheet 2 and place it into spreadsheet 1. 1 sheet has the numbers 0-100 as an example, and the second spreadsheet has the numbers 0-100 and also the corresponding description, and as far as i’ve seen, it’s always going to print out the same set of numbers lack of sales or not, so i’d have thought it would simply be a case of being old fashioned and simply creating a new column, and pasting the buggers in there.

cheers for the reply smile havn’t been on vbforums in a long while, but i got some fantastic help within a few minutes there back in my days of access and such

Profile
 
 
tino
Posted: 28 February 2010 10:13 AM   [ # 3 ]  
Vorsprung Durch Technik
Total Posts:  3937
Joined 
Sparbag - 27 February 2010 01:22 PM

I’ll set this thread to notify me by email but do not hesitate in going to vbforums where I am also a member, but concentrate on programming issues. I can only apologise to the forum administrators if directing forum members to a different forum is unacceptable behaviour.

I personally have no problem with you referring people to another forum which would answer there query alot quicker with more informative information.

Profile
 
 
Sparbag
Posted: 01 March 2010 08:05 AM   [ # 4 ]  
Ace
Total Posts:  1104
Joined 

Hi Hugh,

Below is a first, and untested portion of code explained throughout with comments. Before you use this code I would advise you to make a copy of BOTH. Do not run untested code on your operational version until the code is proven to be correct. Also make sure that all files are saved before running the code. Multiple loop operations tend to lead to scenario’s whereby they remain continuous.

I wrote this using Excel 2007. If you have never written code for 2007 then you will have to show the developer ribbon. Once complete Select the developer tab and choose the Visual Basic console.
Inside the Visual Basic Editor create a new module for the code to go into. Paste the code and save the file as an xlsm or the macro will not run.

It may be the case that you wish to make changes relating to the columns where data is stored in both the remote and local files. the method for doing this is .Cells(row, col). Also be aware that I have assumed that the sheets containing the data are both Sheet1, and are named “Sheet1” respectively. Change if this is not the case.

I doubt very much that this code will work first time, but will not require much tweaking. As previously stated I will continue to receive emails for this thread and will endeavour to reply as quickly as possible.

Sub ShiftData()
    'This procedure opens a remote Excel file to match IDs contained in column 1 of both files
    'The two files are assumed to have only 2 columns of data and that the third column of the
    'local file and that the third column is the primary location of the pulled information
    
    'Declare Variables
    Dim wkbk As Workbook                'workbook object
    Dim wsht As Integer                 'worksheet counter
    Dim row As Integer                  'row counter
    Dim localcount As Integer           'row counter if more than 256 rows convert to double
    Dim remotecount As Integer
    localcount = 2                      'start the counter at the second row to avoid headers
    remotecount = 2
    
    
    'Get the remote file
    'I have found that using absolute addressing more accurate and less likely to fail
    Const ExcelDoc = "C:\My Folder\Name of the file to pull data from.xls"
    Set wkbk = GetObject(ExcelDoc)      'Get the remote document
    Set wsht = wkbk.Worksheets("Sheet1")  ' set the remote sheet
    
    
    
    'Match the contents of the first column
    'start the outer loop sequence
    While Sheets("Sheet1").Cells(localcount, 1) <> ""
        'start the inner loop sequence
        While wkbk.Worksheets(wsht).Cells(1, remotecount) <> ""
            'compare the cell contents
            If wkbk.Worksheets(wsht).Cells(remotecount, 1) = Sheets("Sheet1").Cells(localcount, 1) Then
                'the cells have the same ID set the local cell column to match the remote cell value
                Sheets("Sheet1").Cells(localcount, 3) = wkbk.Worksheets(wsht).Cells(remotecount, 2)
                'increment the local sheet counter
                localcount = localcount + 1
                'reset the remote cell counter to restart the search
                remotecount = 2
                'close the loop
                Wend
            Else
                'cells do not match increment the remote cell row counter
                remotecount = remotecount + 1
            End If
        Wend
        'I think there should be an increment for the local counter here in case there is not a matching ID
        'But as the code is untested it could lead to missed rows
        'localcount = localcount + 1
    Wend
    
    
    'Clean up memory
    'Remove the Excel worksheet and workbook objects from memory
    Set wsht = Nothing
    Set wkbk = Nothing
End Sub

Kind regards

Steve

Profile
 
 
Niche
Posted: 06 March 2010 06:46 PM   [ # 5 ]  
Green
Total Posts:  4
Joined  2009-12-12

I know this does not solve your current problem but in future you may want to consider using acceess as your data processor
Very easy to merge and import databases with access

Profile
 
 
   
 
 
‹‹ Disable video preview thumbnails in Windows 7...      Unsupported video not letting me install XP... possible switch I can use? ››