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.

Solved: Excel Question: Removing unwanted Rows, moving some rows to new columns

Discussion in 'Business Applications' started by Brian Rohan, Mar 30, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. Brian Rohan

    Brian Rohan Thread Starter

    Joined:
    Mar 30, 2009
    Messages:
    66
    I do a weekly report that I base on results that I get with search done in Internet Explorer. I can then cut and paste these results into an Excel spreadsheet, but I need to delete most of the information (whole rows) and reorganize the remaining rows by taking the even numbered rows and putting them into the B column, then delete that Row as well. I have included some images that should help. A few more items:

    1.)The information that I paste into the excel spredsheet may contain up to, but no more than 350 lines from the original copying source in Explorer (I'm not sure how many lines that translates into Excel)

    2.) After every 20 records in Explorer there is a "top" link that gets copied, which needs to be taken into consideration when deleting the extra rows entirely

    3.) Another way to look at this is that I ONLY want to keep the rows that have the 7 digit number a space then 2 more digits, as well as the rows that contain the price with the $ For instance the first record in my example I ONLY kept 8055312 11 & $70,000. The price will ALWAYS have a $ and the first set of number will ALWAYS have 7 digits first, no letters.

    Attached is a jpg that shows the various steps, the last screen shot has a few of the cells highlighted in the upper left hand side. Those 6 highlighted cells is all of the information I need, and how I need it presented from the first 3 search records, the MLS number and the price.
    Thanks!
     

    Attached Files:

  2. Zack Barresse

    Zack Barresse

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

    My question to you is, why aren't you using a web query? Wouldn't that be easier? What is the website you're using here?
     
  3. Brian Rohan

    Brian Rohan Thread Starter

    Joined:
    Mar 30, 2009
    Messages:
    66
    The website I am using is our Multiple Listing Service. It will only allow us agents (not brokers) to have access to what it shows up in the search results, or the whole property information (pics etc) This is the best format they give for me to be able to do what I want (unless I want to pay thousands, take a huge number of classes and get my broker's license). What I do now is take the information in the first page, I print it out for all the areas. I then go group the information by price ranges (0-200K, 200-250, 250-350, 350-500, 500-750 etc) I then count the number in each and put it into a spread sheet (20+ areas all together).

    If I can get the information put into the format above i can easily tell how many homes are in the 0-200, delelte those rows then easily tell 201-250 etc versus now counting them by hand which takes hours to do. If there is a way to go into the results from the site, or process it differently I am ALL for that!

    Thank you
     
  4. Brian Rohan

    Brian Rohan Thread Starter

    Joined:
    Mar 30, 2009
    Messages:
    66
    I did just do a search, and when the results page came up, right clicked in the middle and selected export to Microsoft Excel. Excel opened, it said getting data, then an error came up saying that it was unable to get the data. Any suggestions? I am using IE 7.0.5730.13 and Excel 2003. The RMLS only lets me log in with Explorer, not my firefox if that makes a difference (I am sure Firefox or Greasemonkey could make this work easier).

    Thanks again
     
  5. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    "Another way to look at this is that I ONLY want to keep the rows that have the 7 digit number a space then 2 more digits ..."

    Not sure about that, since the 2nd number seems to be 7 digits - space - 1 digit.

    But possibly there are other constants you could work with.

    In your first (spreadsheet) picture, the number is always above "DETACHD". Of course it might be something else for other records, but if you could make a list of everything it might be (if something other than "DETACHD"), you could work off that.

    The same goes for the $ amounts, in theory; they all appear to be above something like "T H M D" (or could be T H M O ; jeez I need a sight test :eek: ).

    So: tell us about those "elements".
     
  6. Brian Rohan

    Brian Rohan Thread Starter

    Joined:
    Mar 30, 2009
    Messages:
    66
    If you look at the first screenshot the information is presented from left to right, and when it is copied and pasted in Excel it all goes top down. The price will always (well it has for years past, and the MLS may change but not in the near future) be either in front of the THMD in the MLS site, or above it once it is in Excel, the same goes for the 7 digit number plus either the extra 1 or 2 digits (the first number is the property ID number, the next number are how many pictures are available for the listing, anywhere from 0 - 16.) So yes those positions will be static for the forseeable future.

    To add a little bit more to the mix, the data I showed you is from the section called "active" after this data, there are other titles such as withdrawn, pending, sold, cancelled that I would also like to work with if possible in the same manner. Here is how it is layed out in the MLS

    Active

    8046574 11 Detached etc etc $80,000 THMD
    9012574 8 Detached etc etc $90,000 THMD

    Withdrawn

    8098765 1 Detached etc etc $110,000 THMD

    I would like it to translate into the spreadsheet as:

    8046574 11 $80,000
    9012574 8 $90,000

    Withdrawn

    8098765 1 $110,000


    I hope that helps.
     
  7. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Err, OK. Let me try another tack. :D

    "I can then cut and paste these results into an Excel spreadsheet"

    That's part 2 of your jpeg, where you have all of column A is highlighted, yes?

    So:

    1. Get some results like that and paste them in at A1

    2. Enter this in B1:

    =INDIRECT("A"&ROW()*13-12)

    and copy it down some.

    3. Enter this in C1:

    =INDIRECT("A"&ROW()*13-4)

    and copy it down (the same) some (as in 2).

    Are we there yet?
     
  8. Brian Rohan

    Brian Rohan Thread Starter

    Joined:
    Mar 30, 2009
    Messages:
    66
    I'm sorry, I am really very new at this I have used excel for data I have input and done very basic macros. I did what you said to do and it didn't return results that I really could make sense of.


    This might be a better approach

    Compare each cell in Column A for 1 of multiple variables, does the cell contain a $, or does it contain a value with at least 7 digits? if it does, leave the row alone, if it does not, the delete that entire row (even if a row's first cell is blank). I think that what that would leave us are rows that have the 7 digit + 2 digit property ID, and below that the price for said property. Then possibly a second script, or possibly one nested in the first that then reviews all the remaining cells again, if said cell contains a $ then cut that cell and paste it up one and to the right one (A2 goes to B1), then delete the row that contained the original cell (A2) with the $ thereby moving all prices across from their correct property ID.

    Does this help at all?
     
  9. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    In what way does your initial layout differ from the attached?
     

    Attached Files:

  10. Brian Rohan

    Brian Rohan Thread Starter

    Joined:
    Mar 30, 2009
    Messages:
    66
    DUH, didn't think about attaching a couple of original docs. Attached are 2 documents. The first is how the info is originally, the second is how I would ultimately like it to appear (although I deleted a lot of info just to make it simple, but the first couple of examples are what I want, all info is tossed except property ID and price) I also included a screen shot of what I copy and paste.

    In all finality, scan each cell in column A, keep the entire row if it contains a 7 digit number + a 1 or 2 digit number, if it has a $ or if it contains the text Active, pending, sold, withdrawn, bumpable, or cancelled. If the cell in column A does not contain any of these delete the entire row (even if it is blank), then move the price to be beside the property ID and delete the row that used to contain the price before it was cut and pasted beside the property ID.

    I really appreciate your patience with me on this!
     

    Attached Files:

  11. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Maybe I'm barking up the wrong tree. :confused:

    In your original, use this formula in B1:

    =INDIRECT("A"&ROW()*13-12)

    and this in C1:

    =INDIRECT("A"&ROW()*13-4)

    Select both of these and copy. Then select B1:C5 and paste. Copy B1:C5 again. Select A1 on Sheet2 and Edit > Paste Special > Values > OK.

    Back on Sheet1, delete columns B & C. Then delete cells A1:A80 (A81 is the first "ID" for your next category, yes?) and repeat for the second category.

    Isn't that taking out a lot of the donkey work?
     
  12. Brian Rohan

    Brian Rohan Thread Starter

    Joined:
    Mar 30, 2009
    Messages:
    66
    I did as you said and pasted the formulas into the cells of B1 and C1, worked great, got the info in the right cells. I then selected cells B1 and C1 at the same time and copied then (ctrl c, both cells had moving dotted lines around them). I unselected those 2 and then selected a square from B1 to C5, I then tried to paste and it would not let me for some reason. I must be doing something wrong in the various copy and paste's.

    Also, if this works as it should is it possible to make this into something that will work automatically versus doing all the cutting and pasting. This one process I am talking about is for one area in my county, there are 20, so it would be cumbersome to do all this cutting and pasting 20 times over.

    Thanks
     
  13. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    It's hard to ID a constant to base automation on.

    1. Are you able to use macros (AKA VBA) in your environment?

    2. Can you categorically state all possible values that may appear under ID? So far all we have is "DETACHD"; what else might it be?
     
  14. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    "it would be cumbersome to do all this cutting and pasting 20 times over."

    Granted, but at the same time far less cumbersome than what you're currently doing.

    OK, before I turn in ...

    ... if you are able to use macros, press CTRL+Shift+M to run the one in the attached.

    But even if that's close, you must consider:

    (a) what else might there be other than " DETACHD"?

    (b) what else might there be other than "Expired " or "Pending "?

    , because the key to automating is identifying all parameters that the code might have to deal with. HTH
     

    Attached Files:

  15. Brian Rohan

    Brian Rohan Thread Starter

    Joined:
    Mar 30, 2009
    Messages:
    66
    I am using Excel 2003, so I can use VBA. The propery ID will always have 7 digits first, but they will always change, however you are right that it will always have the same relation to the cell that says detached. Which I can see will help greatly, as detached is a constant where the other is not.
     
  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/814184

  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