1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel column manipulation

Discussion in 'Business Applications' started by tyroner, Nov 12, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. tyroner

    tyroner Thread Starter

    Joined:
    Nov 12, 2007
    Messages:
    8
    I have been searching around the forums for something that can do the following, but in vain. Let me explain first what I am trying to do:

    I have many excel files with data arranged in columns under the following headings:

    Sample Variable1 Variable2 Variable3

    but sometimes the file looks like this:

    Sample Variable3 Variable1 Variable2

    or even

    Sample Variable2 Variable1

    What I was trying to do was to take data from each input file and fill a standardized array as an output file (e.g. make them all look like the first set of headings). And if a variable did not exist in the input file, simply to leave that column blank. This is all in the name of science as the data are chemical elements. Any help will certainly aid our research efforts!
     
  2. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Hi there, welcome to the board!

    Are these files all in one location? You're just asking to open a bunch of files, re-arrange the columns as you specified, then close/save them? What are the actual column headings? Are they literally what you posted? Are there only four columns per file? How many sheets? If this data is all related, why are they in different files? Why not in the same workbook but different sheets - or maybe on the same sheet - or perhaps a database?
     
  3. tyroner

    tyroner Thread Starter

    Joined:
    Nov 12, 2007
    Messages:
    8
    Hi there and thank you for your welcome! To answer your questions:

    1. The files can all be placed in the same folder. In fact I have no problem with simply dealing with the manipulation on a file by file basis (I am not dealing with thousands of files)

    2. What I want to do is to re-arrange each file and then save it to a new master file in a standard format.

    3. Column headings might look like this:

    Sample No. Lat Long Detail Analyst MgO SiO2 TiO2 Al2O3 Fe2O3 FeO MgO MnO CaO Na2O K2O P2O5 Cs Rb Ba Th U Nb Ta Pb Sr Zr Hf Y (there are more I havent included).

    The problem is that the headings arent in the same order in every file and in some files the element is not present. So when putting the data into the master file, the process has to take into account missing data and just leave that row blank. There could be 60 headings per file.

    4. Data is related - but the data is from different analysts. The suite of elements analyzed for are similar but not identical.

    5. A database is probably the way to go but to even read this data into a database, the files are going to need to be standardized.

    I am even open to using a non-excel based solution to re-arrange the data columns as a CSV!

    Thank you for responding
     
  4. caraewilton

    caraewilton

    Joined:
    Nov 7, 2007
    Messages:
    1,352
    Do I understand correctly, you simply want the columns to be arranged in the same order on every sheet or workbook?
     
  5. tyroner

    tyroner Thread Starter

    Joined:
    Nov 12, 2007
    Messages:
    8
    Yes - they need to be in the same order but also in the same column so in otherwords say Si has to be in Column A, Na has to be in Column B, Fe has to be in Column C and Cs has to be in column d in everysheet. But if an element is missing say Fe, then if a blank column isnt inserted, Cs would get put into column C by accident.
     
  6. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    You could insert a row at the top of each desired sheet, number the columns accordingly numerically starting at 1. Then sort by those columns, but make sure you do a right-to-left sort by row 1. We could make code for you to loop through files in a folder and then apply code to do this. There are several examples of this type of action on this board. I think I've seen Rollin_Again post this type of code about once a month. ;)

    Edit: Btw, the way over any blank element columns would be to add them to the right of the existing columns but leaving the data blank and assigning it a number. So a sort would still work, but you would need to add the column headers. Again, this can be done via VBA code as well.
     
  7. caraewilton

    caraewilton

    Joined:
    Nov 7, 2007
    Messages:
    1,352
    Firefytr knows his stuff. If it does not work, upload a sample file and I might have another solution. But what I have in mind is a little complicated so first try his idea and see if it works for you:)
     
  8. tyroner

    tyroner Thread Starter

    Joined:
    Nov 12, 2007
    Messages:
    8
    Thanks for the post!
    I did try that and the sorting worked the problem is applying it to multiple files where the elements are totally out of sequence. If you have 60 elements, trying to remember which one is number 3 and which one is number 59 is tough. Also, its almost impossible to know (before sorting) all of the elements that are missing from a given file. I did find a macro (from another post) that would sort columns but the blank column problem is still present!

    Sub MoveColumns()
    Dim wsO As Worksheet
    Dim wsF As Worksheet
    Dim i As Integer

    Application.ScreenUpdating = False
    Set wsO = Worksheets("test")
    Set wsF = Worksheets("Final")
    myColumns = Array("P2O5", "K2O", "Na2O", "CaO", "MgO", "MnO", "Fe2O3", "Sample")
    With wsO.Range("A1:H1")
    For i = 0 To UBound(myColumns)
    On Error Resume Next
    .Find(myColumns(i)).EntireColumn.Copy Destination:=wsF.Cells(1, i + 1)
    Err.Clear
    Next i
    End With
    Set wsO = Nothing
    Set wsF = Nothing
    Application.ScreenUpdating = True
    End Sub
     
  9. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    That's no problem. You need to define what element would be 1, which element would be 2, etc for a complete list. If you have 60 you need to define what is what. You could put this into an array if you wanted to, something like 1 to 60 rows, 1 to 2 columns, then just set each element...

    Code:
    dim arrElements(1 to 60, 1 to 2) as variant
    arrelements(1, 1) = "Fe": arrelements(1, 2) = 1
    arrelements(2, 1) = "Na": arrelements(2, 2) = 2
    '... etc
    You can, as you see, setup loops to loop through your worksheets and find each element, if not found, add it. Once all are added, assign numbers and sort.
     
  10. tyroner

    tyroner Thread Starter

    Joined:
    Nov 12, 2007
    Messages:
    8
    Thanks - I may actually give that a try!!
     
  11. tyroner

    tyroner Thread Starter

    Joined:
    Nov 12, 2007
    Messages:
    8
    Thanks - I might just give access a try!
     
  12. tyroner

    tyroner Thread Starter

    Joined:
    Nov 12, 2007
    Messages:
    8
    Hmmm,

    I have to admit my knowledge of VB is somewhere between none and less. I was able to adjust the existing script but I would have no idea where to insert the additional code. I suspect though after looking at the code, that another problem would arise. While there could be 60 elements in columns, there could be 500 rows of data in each file. Is there any way to modify the existing macro I posted that could deal with the blank columns problem? Or will I have to make good friends with a VB coder ;)
     
  13. caraewilton

    caraewilton

    Joined:
    Nov 7, 2007
    Messages:
    1,352
    Like you my VB coding is non existant.
    But it seems like you actually have quite a lot of data here.
    I wonder if it would not be worth your time to import it into something like access. You could saveas a CSV file, then import the stuff into access, using the import table wizards, or even look up querries.
    Just thinking of less comlicated solutions to your problem:)
     
  14. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Posting here means you've made friends with a bunch of VB coders. ;)

    I'm not sure what you mean by blank columns, I thought I explained that already. If/when you import this data into Access, you can have blank columns. Or is there some value you'd like to put in the blank cells?
     
  15. tyroner

    tyroner Thread Starter

    Joined:
    Nov 12, 2007
    Messages:
    8
    Thanks for your help - I think I am going to read a little more about access and try to take it from there. Fun times and light reading!
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/650905

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice