5 Replies Latest reply on Apr 30, 2018 12:51 PM by Okechukwu Ossai

    Combine Dimensions without Null or Duplicates

    Like Like

      Objective: Combine several sheets of data (xlsx, csv), from different sources, that contain one column of similar data (strings containing "Hosts" - "AAAA" through "FFFF").

       

      Obstacles: The "Host" column data is not the same across sheets.  Some may have 3 hosts, another 2 hosts, and another 5 hosts with associated detail for each host.  No one sheet contains all the "Hosts". The final product should be able to list all the "Hosts" in one column, without nulls, and without duplication.

       

      When I create a Combined Field, it will list all the nulls and duplicate the "Hosts":

      Combined Host.PNG

      I have tried creating a calculation using IFNULL, but it strings the duplicates together:

      IFNULL([Host Aardvark],"")+IFNULL([Host Bengal],"")+IFNULL([Host Cheetah],"")

      IFNULL Calculation.PNG

      I have tried adding hosts directly, but it leaves out "Hosts" and strings duplicates together:

      [Host Aardvark]+[Host Bengal]+[Host Cheetah]

       

      The goal is to have it create something like this:

      Host
      AAAA
      BBBB
      CCCC
      DDDD
      EEEE
      FFFF
      GGGG
      HHHH

       

      Suggestions on how to do it?  I have searched the threads for hours and tried several things, to no avail.  It seems so simple, but the answer eludes me.

       

      The sample workbook is attached.

        • 1. Re: Combine Dimensions without Null or Duplicates
          Okechukwu Ossai

          I can see you did a full outer join. This might not help because the Host names are slightly different in each table. Have you considered doing a union instead?

           

          For union to work, try having similar number of columns, with same column names and data types. If there is no data for a particular column/field in a data source then leave those rows blank. I don't really understand your data structure and what you are trying to achieve but I believe union might be a good solution for you.

           

          However, if you want to keep your current data join and create a single host field, then you can use the formula below;

           

          Create calculated field [Host]

          IF ISNULL([Host Cheetah]) AND NOT ISNULL([Host Aardvark]) THEN [Host Aardvark]

          ELSEIF ISNULL([Host Cheetah]) AND NOT ISNULL([Host Bengal]) THEN [Host Bengal]

          ELSE [Host Cheetah]

          END

           

          Hope this helps. See attached workbook

          Ossai

           

          2 of 2 people found this helpful
          • 2. Re: Combine Dimensions without Null or Duplicates
            Like Like

            Okechukwu,

             

            Thanks for the quick response.  That may work!  Though if I have 8-10 spreadsheets instead of 3, it could get a little lengthy, but still work.

             

            You may have some more ideas based upon what I'm trying to do:

             

            Each spreadsheet (approx 8 total), for the most part, contains unique data.  Each sheet will have different columns of data, however, they all have in common the "Host".  The goal is to be able to have one column of "Host" and add/sort on various dimensions to see which "Hosts" those dimensions apply to.

             

            1. Based upon this, do you think a union would work better in this case if the columns are different for each of the 8 spreadsheets?

            2. If I want to display only certain hosts that have values in a certain column (e.g. "Version"), would union be the best or keep as you've shown?

             

            End Result Sample (for thousands of lines/30+ different columns):

            EndResult.PNG

            • 3. Re: Combine Dimensions without Null or Duplicates
              Okechukwu Ossai

              Hi,

               

              From what you described, Union will help you avoid writing long calculations. However, adding 8-10 column names to the calculated field may not be too much of a hassle either. So it depends on you, both approaches will give you the correct result.

               

              Could you please mark this as the correct answer if it resolved your question?

               

              Hope this helps.

              Ossai

              • 4. Re: Combine Dimensions without Null or Duplicates
                Like Like

                It was easiest to make the calculation for the sheets.  So far everything is going well.  I think your recommendation solved the issue.  Thank you!

                • 5. Re: Combine Dimensions without Null or Duplicates
                  Okechukwu Ossai

                  You're welcome. Glad it helped.