1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

How can you align matching rows in Excel?

Discussion in 'BlackHat Lounge' started by karma05, Jan 27, 2016.

  1. karma05

    karma05 Regular Member

    Joined:
    May 30, 2013
    Messages:
    383
    Likes Received:
    17
    Location:
    Ramsdale
    I'm having a bit of trouble figuring out how to do this in Excel. I have 4 columns and a large amount of rows. Like below:

    link1 345 link5 330
    link5 327 link4 180
    link3 894 link2 660
    link4 174 link3 900
    link2 653 link1 350


    I need the rows of columns A and C to match up WHILE taking the numbers to the right of them with them as they shift.

    Here is an example of how I need it to look:



    link1 345 link1 350
    link2 653 link2 660
    link3 894 link3 900
    link4 174 link4 180
    link5 327 link5 330

    I've tried asking at excel forums etc, but can't seem to get helpful answers. This is my last resort place to ask.

    Can this even be done?

    Thanks.
     
  2. fanatik1389

    fanatik1389 Regular Member

    Joined:
    Apr 7, 2014
    Messages:
    300
    Likes Received:
    106
    Cut C and D. Sort A and B by name (or whatever you want). Sort C and D by same criteria. Paste C and D back.
     
  3. karma05

    karma05 Regular Member

    Joined:
    May 30, 2013
    Messages:
    383
    Likes Received:
    17
    Location:
    Ramsdale
    It's not quite as simple as that. The columns C and D will have less rows than columns A and B due to Scrapebox errors that may occur.
     
  4. fanatik1389

    fanatik1389 Regular Member

    Joined:
    Apr 7, 2014
    Messages:
    300
    Likes Received:
    106
    That makes it a bit more interesting. :)
     
  5. karma05

    karma05 Regular Member

    Joined:
    May 30, 2013
    Messages:
    383
    Likes Received:
    17
    Location:
    Ramsdale
    Would you happen to know a solution?
     
  6. ChanzGrande

    ChanzGrande Elite Member

    Joined:
    Feb 16, 2008
    Messages:
    2,487
    Likes Received:
    1,177
    Occupation:
    Accountant
    Location:
    Northern Woods Counting Money
    Well it will take some sort of macro to accomplish your goals. If you have blank cells, you will need to create a macro that identifies the correct cell data, and moves the lines appropriately. Once you run this macro your cells will line up, and you can perform the simple sort functions you need to do. However, you can only really create a macro if there is an identified pattern in the data errors. For example, if every time there is an error it simply leaves a cell blank, but places the content in a specific cell ... then it is easy to create a formula to correct the errors.

    If the scrapebox errors cause the data to be randomly corrupt with various empty cells and no logical manner with which to CONSISTENTLY identify the correct cell, then the only option may be physical correction - which a VA will happily do for VERY little money since it's particularly easy and only time consuming. Wish I had better advice! Another option is to consider removing the sources that cause the scrapebox errors assuming they can be easily identified - which is typically the case.

    Send me a link by PM to a small data sample file in .csv format, and I will poke around and see if I can macro that sh$t up!
     
  7. karma05

    karma05 Regular Member

    Joined:
    May 30, 2013
    Messages:
    383
    Likes Received:
    17
    Location:
    Ramsdale
    Do you have scrapebox yourself? If so, it would be easy for me to properly explain it to you.
     
  8. ChanzGrande

    ChanzGrande Elite Member

    Joined:
    Feb 16, 2008
    Messages:
    2,487
    Likes Received:
    1,177
    Occupation:
    Accountant
    Location:
    Northern Woods Counting Money
    Have I been unclear in some way? I don't need any help. I'm trying to help you. Of course I have scrapebox, however you reported problem lies in the exported excel data you are scraping, so I thought we were talking about how to fix your problem. I'm here to help - not to have things explained to me.

    Once again, send me a sample of your poorly formatted excel data which is exported from scrapebox, and I will see if I can create a macro to align the cells. Or maybe you don't actually want help? Trust me, I don't give two craps about the content of your .csv file - I'm just offering to resolve a problem - which is basically what I do.

    Let me know if you want your issue resolved. Cheers!
     
  9. fanatik1389

    fanatik1389 Regular Member

    Joined:
    Apr 7, 2014
    Messages:
    300
    Likes Received:
    106
    @op. I have SB. And I'm kinda bored right now. Shoot a pm. I'll do my best to help. :)
     
  10. karma05

    karma05 Regular Member

    Joined:
    May 30, 2013
    Messages:
    383
    Likes Received:
    17
    Location:
    Ramsdale
    Sorry if I offended you davers, I just wanted to know if you had scrapebox so then you'd understand exactly what I mean about how you can get errors with the custom grabber. Not everybody knows about that stuff, I was just making sure.
     
  11. Intruder

    Intruder Regular Member

    Joined:
    Jun 3, 2012
    Messages:
    340
    Likes Received:
    206
    You're saying that you have same data in A and C...?
    If so you can use column C to highlight data that matches the column A, that way you can remove that unmatched rows. I have seen it on youtube, search for conditional formatting.
    Also, you need to first sort A and B, by A only; C and D, by C only to get the row matched after the removal of unmatched data.
     
  12. karma05

    karma05 Regular Member

    Joined:
    May 30, 2013
    Messages:
    383
    Likes Received:
    17
    Location:
    Ramsdale
    Will this line them up though? Because they are often scrambled all over the place down the columns.
     
  13. Intruder

    Intruder Regular Member

    Joined:
    Jun 3, 2012
    Messages:
    340
    Likes Received:
    206
    You have to remove the non-highlighted rows from A(I think that's the column where you have more rows) to get them aligned. If you're looking to keep all the rows then you have to find another solution.
     
    • Thanks Thanks x 1
  14. Conor

    Conor Elite Member

    Joined:
    Nov 7, 2012
    Messages:
    3,577
    Likes Received:
    5,954
    Gender:
    Male
    Location:
    South Africa
    Home Page:
    Simple stuff:

    http://stackoverflow.com/questions/12836396/how-to-align-duplicates-on-the-same-rows-in-excel
     
  15. karma05

    karma05 Regular Member

    Joined:
    May 30, 2013
    Messages:
    383
    Likes Received:
    17
    Location:
    Ramsdale
  16. opahopa

    opahopa Junior Member

    Joined:
    Sep 23, 2012
    Messages:
    163
    Likes Received:
    10
    Hello ! I can try to write a VBA macros for you, just send me an example in .xls file
    I've already done it for example in first post, but not sure about how it will work with a real task.