none
How to List same colums beside each other with different Metrics? RRS feed

  • Question

  • Hello,

    I have two lists. List 1 contains certain Metrics from one Source while List 2 is smaller and contains other metrics.

    I want to connect both List to one List of all metrics. List 2 contains Part of the websites of List 1 and I wan to connect these two Lists that it will be one List. One List where of a certain website I can see various metrics.

    On the Picture List one does have the domains on Row A & B, while List B does have it on row G.

    How connect them together on Row N?

    Here is the Excel List t download

    Excel File to Download

    http://s000.tinyupload.com/index.php?file_id=66174294936032710162

    Thanks for helping.

    Troy


    Tuesday, March 17, 2015 1:59 PM

Answers

  • I feel like with the new list its more simple, because List 1 only contains one extra metric.

    If you want to have a look what I did so far with the new list:

    Hi Troy,

    in your new file, you compare in the other direction, your formula
    I8:  =MATCH(A8&"*",D:D,0)
    returns the row number in List2 where the URL from List1 is found in List2.

    So we can copy all items from that row in List2 to the same row in List1 (for testing purpose):
    K8:O8:  {=OFFSET($D$1,I8-1,0,1,5)}

    As you see you get the correct URL from D3646 into K8 from List2 (and the other 4 values from E:H into the cells on the right).

    But you have the same problem as before, have a look into I14:I15, as you see you also have double numbers here. And there are many more, up to 6 times the same number.

    That is the main problem, we can only guess which item of List2 belongs to List1. Even if we use an other (more tricky) comparison algorithms, the result is only a guess. Whether the comparison is sufficient in your case you have to decide for yourself.

    And there is another issue (when you compare in that direction) e.g. cell I4245 shows a 7 and this is wrong, because row 7 contain the headings. That is no error within the formula, it is due to A4245 which is empty and "*" match an empty cell. So I suggest that you change the formula in I8 to
    =IF(ISBLANK(A8),NA(),MATCH(A8&"*",D:D,0))

    Andreas.


    Monday, March 30, 2015 1:43 PM

All replies

  • Hi,

    Are you going to make a compare for column A and Column B, if a cell value shows in ColumnA and Column B , then insert the value to Column N . Am I right?

    If it is, you can find each cell value in Column B, if the cell value is in Column B, then write it into Column N

    Hope this could help you.

    Best Regards

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, March 19, 2015 9:15 AM
    Moderator
  • I Dont understand what you mean.

    What currently is the situation is this:

    List 1 List 2

    A B C D F G H

    Website A Metric 1 Metric 2 Metric 3 Website B Metric 4 Metric 5
    Website C Metric 1 Metric 2 Metric 3 Website A Metric 4 Metric 5

    What I want is this:

    A B C D E F

    Website A Metric 1 Metric 2 Metric 3 Metric 4 Metric 5
    Website B Metric 1 Metric 2 Metric 3 Metric 4 Metric 5
    Website C Metric 1 Metric 2 Metric 3 Metric 4 Metric 5

    So basically I want to match that Website 1 on List one is match with the same Website of List 2, the metrics get combined and I have a clean list with all metrics sorted in the right way.

    How to do it? Whats the formula?

    Thanks

    Troy

    Friday, March 20, 2015 10:00 AM
  • Damn conversion.

    Is there anyone who can help me out?

    What is unclear to understand?

    Take care

    Troy

    Wednesday, March 25, 2015 5:04 PM
  • "What is unclear to understand?"

    Pretty much your whole question.

    I've looked at the Excel file you provided and can't find where anything in list1 matches what's in list2. Which columns do you want to match? If I look at col's B and G, the actual content is different. For example

    ColB: http://10jaarfeest.freegb.nl/?gb_page=2,BuyGenericViagra.com

    ColG: http://10jaarfeest.freegb.nl/?gb_page=2

    Same site but different strings. I looked at Agro-max.ru, same deal. These may be exceptions to the rule, but I'm not going to review 8000 rows of data looking for stuff that's the same.

    Let's assume that there is data in ColA and ColG that match. OK, what EXACTLY do you want in ColN. You say 'metrics'...ok, what metrics?

    I'm not the sharpest pencil in the box so maybe your question means something to folks who deal with web-sites but I'm just not getting what you want.

    Wednesday, March 25, 2015 5:49 PM
  • Excel 2010 with free Power Query Add-In.
    Compatible with Office 2013 Pro Plus.
    Incomprehensible.
    Ditto Dogubob.
    But here is a nudge that applies to your posted example on 03/20 10:00 AM.
    My example merges rows with the same name (Websites)
    with specific categories (rules, risks).
    Power Query will become comprehensible after 3 months of study.
    http://www.mediafire.com/view/wdo51didkg4wdyk/03_25_15.xlsx

    Thursday, March 26, 2015 2:05 AM

  • What is unclear to understand?


    Well, the issue is that you think you there is a simple solution for that. Such things are very complicated and in most cases not possible, resp. guessing games.

    The only thing in List1 and List2 that we can "compare" is the URL from column B and column G. As List2 is shorter as List1 we can try something to merge List2 to List1...

    We need 7 empty columns, so insert some columns till column T is empty. Write this headings in the cells:

    N7:  URL row List2=>List1
    O7:  URL row List1=>List2
    P7:  SourceTrustFlow
    Q7:  SourceCitationFlow
    R7:  Domain
    S7:  DomainTrustFlow
    T7:  DomainCitationFlow

    We can search for a URL in List1 which start with the URL in List2:
    N8:  =MATCH(G8&"*",B:B,0)

    Drag the formula down to the end of List2. As you see there are duplicates, means the results are not perfect.

    We want to get the fields from List2 to List1 so we need the row number of the found row in List1:
    O8:  =MATCH(ROW(),N:N,0)

    Then we can copy the items from List2 into the row of List1, note that this is an array formula.
    P8:T8:  {=OFFSET($I$1,O8-1,0,1,5)}

    Drag this formulas down to the end of List1, then hide column G:O, they are now helper columns only.

    Andreas.
    • Marked as answer by L.HlModerator Monday, March 30, 2015 8:09 AM
    • Unmarked as answer by TroyMaster Monday, March 30, 2015 9:40 AM
    Thursday, March 26, 2015 2:11 PM
  • Okay. I was listen to you. I was carefully thinking about what you all said and tried a lot. Spend the last 1-2 hours trying to figure out this.

    I think I didnt made it clear enough. I apologize for it.
    And I underestimated how complicated it will be.

    So I tried to come up with a minimum viable version of this where I would be satisfied.

    I changed a bit the structure of the list, upload better pictures and hopefully you can help me better.

    To download the file, click here:

    Excel File

    http://s000.tinyupload.com/index.php?file_id=78987840536246831875

    Andreas. I love your help so far. You helped me before in an awesome way and it seems like you are into the right thing again. Thought when I try to do the formula,

    numbers are coming out for the formula
    =MATCH(C8&"*",A:A,0)

    And with the formula

    =MATCH(ROW(),N:N,0) I get an error.
    Does it mean I have to put in something at (ROW([insert here what???]] ???

    And Im not sure what you mean by

    P8:T8:  {=OFFSET($I$1,O8-1,0,1,5)}
    When I try it, I dont even get a formula changed with it.

    Excel SheetThe perfect endresult

    Monday, March 30, 2015 9:55 AM
  • And with the formula

    =MATCH(ROW(),N:N,0) I get an error.
    Does it mean I have to put in something at (ROW([insert here what???]] ???

    And Im not sure what you mean by

    P8:T8:  {=OFFSET($I$1,O8-1,0,1,5)}
    When I try it, I dont even get a formula changed with it.

    Please be indulgent with me, I use your original file from your first post, because I already have the formulas in there.

    https://dl.dropboxusercontent.com/u/35239054/Samples/05478a84-743c-4f1f-a027-a35ce30a2e64.xlsx

    When you see a formula surrounded by {} means this is an array formula. To enter the formula above select the cells P8:T8, copy the formula into the Formula Bar, then press CTRL-SHIFT-ENTER and you see that the {} appear.

    Anything what I have modified is blue, with the created grouping you can hide G:O as I said.

    Andreas.

    Monday, March 30, 2015 10:27 AM
  • Ahh okay. Now I understand what you mean by array formula.

    Hmmm I was looking at the excel file. 

    It seems like there are MANY MANY times no match between the two lists/ domains.

    And when I search manually, I can find that there are exact matches. 

    It seems like there must be a better way to do it???

    I tried to apply what you did with the former list to get it on the new list and its kind of the same problem (I hoped that that might change).

    Also array formula are completely new to me. So Im not even sure which parameters I have to change in order to make it work for the new list.

    Hmmm any better solution? I feel like with the new list its more simple, because List 1 only contains one extra metric.

    If you want to have a look what I did so far with the new list:

    New Excel File

    http://s000.tinyupload.com/index.php?file_id=04768414635512520013

    Take care 
    Troy

    Monday, March 30, 2015 12:31 PM
  • I feel like with the new list its more simple, because List 1 only contains one extra metric.

    If you want to have a look what I did so far with the new list:

    Hi Troy,

    in your new file, you compare in the other direction, your formula
    I8:  =MATCH(A8&"*",D:D,0)
    returns the row number in List2 where the URL from List1 is found in List2.

    So we can copy all items from that row in List2 to the same row in List1 (for testing purpose):
    K8:O8:  {=OFFSET($D$1,I8-1,0,1,5)}

    As you see you get the correct URL from D3646 into K8 from List2 (and the other 4 values from E:H into the cells on the right).

    But you have the same problem as before, have a look into I14:I15, as you see you also have double numbers here. And there are many more, up to 6 times the same number.

    That is the main problem, we can only guess which item of List2 belongs to List1. Even if we use an other (more tricky) comparison algorithms, the result is only a guess. Whether the comparison is sufficient in your case you have to decide for yourself.

    And there is another issue (when you compare in that direction) e.g. cell I4245 shows a 7 and this is wrong, because row 7 contain the headings. That is no error within the formula, it is due to A4245 which is empty and "*" match an empty cell. So I suggest that you change the formula in I8 to
    =IF(ISBLANK(A8),NA(),MATCH(A8&"*",D:D,0))

    Andreas.


    Monday, March 30, 2015 1:43 PM