Convert multiple rows record into one single row

Question:

The worksheet shows some payments under each customer (header). How can I apply the “sum if” formula to copy the customer code and customer name to every “PY” in Column D?

Answer:

You cannot apply SUMIF to copy the customer code and company name to every PY in column D. The formula you should use is IF.

First, you must put the customer code and company in the first row. The best way to do this is to use the formula method. For example, in A1 put the formula =C1 to display the customer code in A1.

Using the IF formula, we can set the condition to pick up the customer code or the company name is column D contains "PY", i.e. in cell A1, enter the formula

=IF(D2="PY",A1,....)

That is the first part.

Now the IF formula has a part to present a value when column D does not contain the text "PY". Specifcally, we are interested in is to return the company name if it ever changes and discards all other values. To do this, we have to find the pattern that we can use to identify that the row contains company name. We can check whether column E is empty. We can use another IF formula to do this. Since we have dealt with "PY" in column D, it will not pick up "PY" again. In this case, it will pick up the customer code when it detect the column E is empty. So we have to add another IF formula into the first IF formula. Now the formula becomes

=IF(D2="PY",A1,IF(E2="",C2,....

If it does not find a blank, return the value that is above the formula, i.e. A1. So the the formula in A2 finally becomes

=IF(D2="PY",A1,IF(E2="",C2,A1))

Cannot format my dates in Excel

Question:

I have a worksheet containing hundreds of rows of data whereby no matter how I tried formatting the “date” (Column D), it still does not shows in ascending or descending sequence but instead it sort by the 1^st  2-digits of the data.

Answer:

The date column is actually not presenting the dates in the right format. They are text formatted probably because your computer is accepting dates with dd/mm/yy format while the dates in your worksheet is in mm/dd/yy. Or it could be the other way round. To resolve this, the fastest way is to use

Data > Text to Columns > Next > Next > Date Format (Select “Date”)” and choose the right date format (dd/mm/yy) for your dates.

After the conversion, you will be able to format the dates in any format you prefer.

Presenting numbers in thousands

Watch the video below and find out 4 different methods to display a number in thousands. The 4th method is real cool! I am sure you will like it.

Mesh two tables of data

Question:
How can i "mesh" two tables of data?  Each block of data has a common column with the person's last name.  When I sort, however, there are missing names in the smaller block of data.  I want excel to link or mesh the two blocks.

Answer:
You can apply the VLOOKUP formula on the bigger table for those fields you want to combine. If the formula find the last name (common field), they would return the values found in the smaller table. If not, you will get a #N/A error which is an indication that the value is not available in the small table. Remember to set the last parameter to FALSE.