Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education

Followers

Copy, group and chronological sorting data from two tables

How to group and sort the data chronologically by date from two tables in Excel

In this tutorial I will show how we can two tables, which contain the same information but different sequences of columns, grouped and automatically sort by date. Take for example that we have two tables that are a result of our bookkeeping. The first table (worksheet "Table1") shows issued invoices and the second table (worksheet "Table2") bank statements. You notice different order of columns in the picture below with their titles. This sequence makes it difficult grouping of data and therefore we have to use extra worksheet and auxiliary tables that would unify the order data.

Issued invoices and copies of invoices from bank

Worksheets "Table1" and "Table2."

These are the original data on which we want to group the numbers of invoices by date and chronologically sorted on one worksheet, in this case on the worksheet "results". Here we have two "real" Excel Table. The range of data on this worksheet is A1:C21.

How to group and sort the data chronologically by date from two tables in Excel

Named ranges of data in Excel


I am always for the naming a range of cells in Excel. I am so in this example named some ranges of data for simplicity and ease of understanding formula. In this tutorial you will see what they look like formula with absolute addresses and what they look like formula with named cells. In the picture below you see named cell ranges in the Name Manager. Also in the Name Manager you notice that Table1 and Table2 represent "real" Excel Table, and all other "range-table" which I marked colors, only ranges are framed cell lines. If you do not know how to create a real or actual spreadsheet in Excel, see Tutorial: Creating real Tables in Excel.

Named ranges of data in Excel 2013

Automatic grouping and chronological sorting invoices by date

Worksheet 'results'

The result that we want, you can see in the image below in the worksheet "results". So we want to Excel automatically sorting by date all the numbers of invoices, and customers who are concerned invoices issued. The range of data in the two tables below to meet your requirements, this depends on the sum of the invoice that you have on native tables (worksheets Table1 and Table2).

Explanation of table in the picture below. Formula you can see at the end of the tutorials in the explanation of each worksheet separately. You notice two tables (table yellow and gray table). In both tables we perform sorting by date. 'Yellow table' has a unique dates and gray table has duplicates date. More on these tables and formulas created at the end of the tutorial.

How to automatically grouped and sorted chronologically issued invoices in Excel

The consolidation of data from multiple Excel table

Worksheet "extra-sheet"

On this worksheet, consolidate data and adapt to their needs, Ie. the required result in a worksheet "results". Example currently reading is just one of my ideas, I believe that there are different and simpler solutions. However, I believe that you heard saying "sometimes aim justifies the means". So here, it does not matter "how?" but it is important to get the result.

Explanation of all the tables, ie. Cell range in the image below:

Yellow table (data range): I am for this example sets the data range A1:C21. This table pulls (copy) data from a worksheet 'Table1' ie. The actual Table1 (please see picture Name Manager). In the yellow table below provides the following formula to be copied down. Since the "Table1" real table in the formulas can be used differently from the usual arguments (in fact Excel will adjust the formula I during the creation of the formula). With the explanation below, see the illustration above Name Manager and named ranges of data.

The formula in cell A2:
=IF(Table1[[#This Row];[date]]<>"";Table1[[#This Row];[date]];"")
The formula in cell B2:
=IF(Table1[[#This Row];[invoice number]]<>"";Table1[[#This Row];[invoice number]];"") 

The formula in cell C2: 
=IF(Table1[[#This Row];[customer]]<>"";Table1[[#This Row];[customer]];"")

Green table (data range): I am for this example sets a range of data E1:G21. This table pulls (copy) data from a worksheet 'Table2' ie. from Table2 in the green table below provides the following formula to be copied down.

The formula in cell E2:
=IF(Table2[[#This Row];[date]]<>"";Table2[[#This Row];[date]];"")
The formula in cell F2: 
=IF(Table2[[#This Row];[invoice number]]<>"";Table2[[#This Row];[invoice number]];"")
The formula in cell G2:
=IF(Table2[[#This Row];[customer]]<>"";Table2[[#This Row];[customer]];"")

By creating these two tables (yellow and green tables), I got the same order of columns from two tables, this is a starting point for further calculations.

In the event that the situation is not, as it is ie. 'Table1' is not real but only the range of data we could use the following formula.

The formula in cell A2 would look like this: =IF(Table1!A2<>"";Table1!A2; "")
The formula in cell B2 would look like this: =IF(Table1!C2<>"";Table1!C2; "")
The formula in cell C2 would look like this: =IF(Table1!B2<>"";Table1!B2; "")

The formula in cell E2 would look like this: =IF(Table2!B2<>"";Table2!B2; "")
The formula in cell F2 would look like this: =IF(Table2!C2<>"";Table2!C2; "")
The formula in cell G2 would look like this: =IF(Table2!A2<>"";Table2!A2; "")


Brown table (data range): I am for this example sets a range of data I1:K41. This table pulls (copy) data from the 'yellow' and 'green' tables. The upper part of this tables includes a range of cells I2:K21 and it copies the data from the 'yellow' table. The lower part of the table includes a range of cells I22:K41 in it copies the data from the 'green'.
In the 'brown' table below provides the following formula to be copied down.

The formula in cell I2 =A2
The formula in cell J2 =B2
The formula in cell K2: =C2

The formula in cell I22: =E2
The formula in cell J22: =F2
The formula in cell K22: =G2

In the brown table in this range of data I1:K41 I named the range of cells I2:I41 named "BlanskRange". If you are wondering why? Because if we called this range of cells to be important in the formula in column M 'red table'. And finally you may wonder why I even created a 'brown' table. In order to organize their information from 'yellow' and 'green' table in column one below the other and facilitate the grouping of data for further calculation.

Red table (data range): I am for this example sets a range of data M1:O41. This table pulls (copy) data from the 'brown' tables and groups them one under the other, with no blank rows or without blank cells. Array formula should be copied down (do not forget to end your Array formula with Ctrl+Shift+Enter).

ARRAY formula in cell M2:
=IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)-COUNTBLANK(BlanksRange);"";INDIRECT(ADDRESS(SMALL((IF(BlanksRange<>"";ROW(BlanksRange);ROW()+ROWS(BlanksRange)));ROW()-ROW(NoBlanksRange)+1);COLUMN(BlanksRange);4)))

ARRAY formula in cell N2:
=IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)-COUNTBLANK(BlanksRange);"";INDIRECT(ADDRESS(SMALL((IF(BlanksRange<>"";ROW(BlanksRange);ROW()+ROWS(BlanksRange)));ROW()-ROW(NoBlanksRange)+1);COLUMN(BlanksRange)+1;4)))

ARRAY formula in cell O2:
=IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)-COUNTBLANK(BlanksRange);"";INDIRECT(ADDRESS(SMALL((IF(BlanksRange<>"";ROW(BlanksRange);ROW()+ROWS(BlanksRange)));ROW()-ROW(NoBlanksRange)+1);COLUMN(BlanksRange)+2;4)))

You notice in the image below in the 'red table', grouped data from the 'brown table' without blank rows. If you look at the picture above the "Name Manager", you could see that I was named a range of cells in column M2:O41 called "group_data". Somehow it seemed appropriate to realize that I have data from two tables amassed into one with no blank cells.

How to create the table or the steps to create a baseline data for further calculation in Excel

Grey table (data range): And finally one more among the table that will be used for further calculation and the desired goal. This table (see picture above) is important in the event that we have duplicates in the field conditions. In this case it is the date of the requirement that we want to automatically sort the invoices. In order to obtain unique information Date, I decided to count the number of the date that is repeated in the column 'M'. That's why I marked 'Q' ie. in the cell 'Q2' by formula (copied down)

=IF(M2="";"";COUNTIF(M$2:N2;M2))

Using this formula, I have insight into the number of repetitions of a given date (ascending). Notice that the date 24.01.2015 (this is european Date) appears twice in the column 'M' and the formula will be for the two dates back ordinal numbers 1 and 2.

In column 'R' by the formula ie. Excel CONCATENATE function, I brought dates and number of repetitions of a certain date. The cells in column 'D' are formatted as "General" so that Excel displays the number instead of date.

The formula in cell 'R2' is as follows:
=IFERROR(VALUE(IF(M2="";"";CONCATENATE(M2;Q2)));"")

This formula uses Excel IFERROR function to prevent the display of errors and VALUE function that converts text into numerical value. Also, note that the date has 5 digits and any date associated with the occurrence digit sequential number in column 'Q'. For example, the date 24.1.2015 (01/24/2015) is actually a numerical value of 42028 when the cell is formatted as "General". This 'gray table' me the basis for sorting on the worksheet "results".

If you look at the picture above the "Name Manager", you could see that I was named a range of cells in column R2:R41 named "glued". Somehow it seemed appropriate to realize that I glued ordinal number of occurrences of the general number of the date.

Automatically sort Dates in Excel

Worksheet "results"

Yellow table (data range): is a variant that we have a unique dates. So, Dates are not repeated. In the this case, this is important because the column 'B' use two types of formulas. In columns 'C' and 'D' use Excel VLOOKUP function that is not good to work with data as provided when carried. In the picture below you notice the yellow table, sorted ascending dates and their associated data. It is important to note that in this case Excel is an copied all the data, we lack a date in yellow table (that is the date 24.01.2015 for the invoice number 4)

Formula in the 'yellow table' are the following:

ARRAY formula in cell B2:
=INDEX(NoBlanksRange;MATCH(0;COUNTIF(NoBlanksRange;"<"&NoBlanksRange);0))

This array formula in cell B2 might look like this:
=INDEX(extra-sheet!$M$2:$M$41;MATCH(0;COUNTIF(extra-sheet!$M$2:$M$41;"<"&extra-sheet!$M$2:$M$41);0))
ARRAY formula in cell B3, copy this formula down:
=IF(COUNTIF(NoBlanksRange;">"&B2);INDEX(NoBlanksRange;MATCH(COUNTIF(NoBlanksRange;"<="&B2);COUNTIF(NoBlanksRange;"<"&NoBlanksRange);0));"")
The formula in cell C2:
=IFERROR(VLOOKUP(B2;group_data;2;FALSE);"")
This formula might look like this:
=IFERROR(VLOOKUP(B2;extra-sheet!$M$2:$A$41;2;FALSE);"")

The formula in cell D2:
=IFERROR(VLOOKUP(B2;group_data;3;FALSE);"")

This formula might look like this:
=IFERROR(VLOOKUP(B2;extra-sheet!$M$2:$O$41;3;FALSE);"")

Grey table (data range): is a variant of "Duplicates in Excel" when we have dates that are repeated, so we need to sort duplicates. In column 'F' we use the same formula as in column 'B', the columns 'G' and 'H' using a combination of Excel functions INDEX-MATCH, which we are to return corresponding data in the same order for the condition. You're probably wondering, from where duplicates date? There is a possibility that the same date we have more invoices issued and therefore will be one and the same date shown several times but we need to sort by giving us the order number of invoices to be ascending. You notice the column 'A' gray color which serves as an extra for the 'gray table'. In the this column we draw dates with the worksheet "extra-sheet" or so the dates are formatted as a number of (general).

Column 'A' is the extra column that pulls (copying) data in column 'D' with the worksheet "extra-sheet". This column is important to us for 'gray table' when we have duplicates date (the conditions under which we sort). This column 'A' is not important for us 'yellow table.'

In this 'gray table' main Excel functions are to us INDEX and MATCH as a substitute for VLOOKUP in some cases because they are more easily manipulated.

Formulas in cells are as follows: You notice how the formula easier when you use named ranges of data.

ARRAY formula in cell A2:
=INDEX(glued;MATCH(0;COUNTIF(glued;"<"&glued);0))

This formula might look like this:
=INDEX(extra-sheet!$R$2:$R$41;MATCH(0;COUNTIF(extra-sheet!$R$2:$R$41;"<"&extra-sheet!$R$2:$R$41);0))

ARRAY formula in cell A3, copy this formula down:
=IF(COUNTIF(glued;">"&A2);INDEX(glued;MATCH(COUNTIF(glued;"<="&A2);COUNTIF(glued;"<"&glued);0));"")

This formula might look like this:
=IF(COUNTIF(extra-sheet!$R$2:$R$41;">"&A2);INDEX(extra-sheet!$R$2:$R$41;MATCH(COUNTIF(extra-sheet!$R$2:$R$41;"<="&A2);COUNTIF(extra-sheet!$R$2:$R$41;"<"&extra-sheet!$R$2:$R$41);0));"")
Using the formula in cell 'A2' and 'A3:A100' automatic sort ascending numeric values.

The formula in cell F2 and copy it down: =IFERROR(VALUE(MID(A2;1;5));"")
This formula using Excel functions MID extracted with the first five digits of numeric values which is in fact a general value for a date but its result is a text format, VALUE converts text into the numerical value and Excel displays the date when you format a cell for date display (Format Cells).

The formula in cell G2 and copy it down: =INDEX(extra-sheet!$N$2:$N$41;MATCH(A2;glued;0))
The formula in cell H2, and copy it down: =INDEX(extra-sheet!$O$2:$O$41;MATCH(A2;glued;0))

Automatic sorting date repetitive

You notice the formulas in the cells 'G2' and 'H2'. I deliberately did not want to appoint the ranges of cells in columns 'N' and 'M' on the worksheet "extra-sheet" that you have understood and observed difference simplicity of the formula when it comes to the absolute addresses and appointed data.

And finally, you can see the results in the 'gray table'. We got the desired result and that the data sorted by date in ascending order no matter what we are repeating dates. In column 'F' format the cells in the date format. Of course, the column 'A' can be hide.

I hope that I am able to explain the understandable example in this tutorial.