Tech at the speed of Warp

Past week I had a really big problem. There was a document that I needed to make each and every day.

The problem consisted of having one unsorted sheet with diverse data and as it is not constant, on one day a piece of data would be in row 113 and the next it would be in row 65, it would be simply a mammoth mask to undertake each day in organizing the data to fit the table.

The picture on the left is a sample of one such table. Code column is scrambled and you have to unscramble it every day. Now imagine that there are more than 100 or 1000 such entries.

First we need to create on another sheet (in this case it will be Sheet1 and unsorted table on Sheet2) a layout of how we want our report to look like. On the right is yet again a sample of how it could look. Note that the code Column is sorted. This is our reference for extracting data from the table. It could contain any form of data (text, date etc). There is no specific need for code column to be the reference one, it could have easily been the Value or Name column.

Now comes the "Hard" part. In columns that we wish to populate we need to enter this formula:

=INDEX(Sheet2!B:B,MATCH(A3,Sheet2!A:A,0))

There are two functions in this formula. First is INDEX and the second is MATCH.

INDEX(Sheet2!B:B,MATCH(A3,Sheet2!A:A,0))

• Sheet2 is from where we will take the data
  
• B:B identifies the column from which the data will be taken
  
• MATCH(A3,Sheet2!A:A,0))
  
  • A3 is that we are looking for and in our case it is the Code (product code, etc.)
    
  • Sheet2 is where we will conduct our search
    
  • and A:A is the range in which we will search. A:A means the entire column A.
    
  • 0 is a Match Type argument number and for more information on it type MATCH in Excel Search bar. With this set to 0 there is also a possibility of doing searches with wildcards like * or ?. Where * is any length or letters sequence sequence and ? stands for just one letters character. This does not work with numbers.
    
  • All of this together searches entire column A in Sheet2 for value in A3 on Sheet1 and returns the row in which that data was found.

INDEX takes information about the row number that is provided by MATCH and completes the formula so that we get the data from the row we want and from column B on Sheet2.
After entering the formula we can copy it to others and we have our self a report that stays the same regardless of how our data change. The only prerequisite is that data is sorted into columns that don't change places.There is additional improvement on the original that does not return errors if nothing was found but more on that later.

If any questions or comments please feel free to contact me.

Labels: Excel, Programing