Announcement

Collapse
No announcement yet.

Sort and Match Uneven Columns of Data

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Sort and Match Uneven Columns of Data

    How can i go about sorting and matching two columns or more of data if the columns are uneven? (See Simplified Pic below) Basically I need the rows in the first two columns to match with the according data in the last two columns. A simple sort will not work because there are not the same numbers in the RA# and AF# fields.


  • #2
    Re: Sort and Match Uneven Columns of data

    Assuming your match criteria reside under RA# and AF# columns, you can populate either of the two sets of data using a combination of INDEX and MATCH functions.

    Are you familiar with these?

    m

    Comment


    • #3
      Re: Sort and Match Uneven Columns of data

      not really, could you provide an example? I just need to make sure that the ra# and quantity columns match up with the af# and quantity column.
      so it would go from

      to
      Last edited by Jack in the UK; November 4th, 2005, 09:36. Reason: Tidy post / Edited subject title - jiuk

      Comment


      • #4
        Re: Sort and Match Uneven Columns of data (Still Can't Get It)

        To get your results to appear exactly like you have them in http://www.benrabicoff.com/match2.jpg, you need to have the original RA# List somewhere other than in Columns B & C, as this is where you'd like the final output (sorted list) to appear. To avoid confusion, let's make your sorted RA# list appear in columns H and I rather than B & C. That is, the sorted RA#'s will appear in column I and corresponding quantities in column H.

        The following formulas can help you achieve this.


        in cell I2, you will need to type in the formula:

        =IF(COUNTIF($C:$C,E2)=0,"",VLOOKUP(E2,$C:$C,1,0))


        in cell H2, you will need to type in the formula:

        =IF(I2="","",INDEX($B:$B,MATCH(E2,$C:$C,0)))


        Both the above formulas may be copied down till the last row of data.

        HTH

        m

        Comment

        Working...
        X