I have a column A with repetitive dates. Column B with repetitive names Example: 05/01/09 a 05/02/09 b 05/03/09 a 05/01/09 c 05/04/09 a 05/01/09 d 05/03/09 c I want to count how many a,b,c,... I have for each date.

Merging Columns In Excel Now that we've clarified what merging columns actually means, we can explore how to do it. The first step is to perform the merge for the first cells. Let's go back to our first example and suppose that we are merging column A that contains first names with column B that contains second names. We'll put the merged columns into column C. To merge cell A1 with cell B1 we woul type the following into cell C1:=A1&" "&B1


paste this into C1 (or where needed)
=A1&" "&B1

Hi JK,
We have to use the combination of dynamic list and indirect() function.
and offcourse countif() and if().

If you need the solution from me, Please send me the sample sheet on [email protected] so that I can send you the solution sheet.

Zulfikar Ali

I am assuming that you want your date column to run daily - not updating itself with todays date over previous data....

In which case your column will run a daily date, You can achieve this by typing a date in the format you choose = 01/05/09, 1 May 09, etc hit return. scroll on the bottom corner of the cell you have typed the date in until it changes from a large white cross to a smaller cross and drag this down the column.

For the totals that need to subtotal autmoatically you will need to select a range - so is this total a weekly/ monthly total - How large is your range.

Once you have decided on the above - for example the in Column A the date ranges from 1st May - 31st May for a calendar month, the subtotal will need to include 31 cells - so in the Column B which is the subtotal = SUM(B2:B32)

You can copy and paste the sum into adjoining cells if you are seperating income from expenses etc.

Hope this helps.

You need to go into format cells and then merge them together and you should be just fine. Highlight the cells that you wish to merge and then right click and then click on format cells and then merge cells together.

I'm certain that I can solve your problem...

Can you send me a xls file with you data to my e-mail ([email protected]) so that I can try with real data ?

Send these explanation with your email to !

Thanks

what is it that you are using.

there are 1000 of things one can add to DT.

what.?

COUNTIF counts items that meet criteria. The SUMIF will "add" the amounts. I believe you want to use the COUNTIF() function.

Here is one way to do it:
1. In column A enter the name
2. In column B enter their start date in the format day-month-2008 (31-12-2008 for Dec. 31, 2008); if a day is less than 10, put zero in front (03, 04, etc.)
3. In column C put 31-12-2008 (December 31, 2008, the last day of the year)
4. In column D, put C1-B1 and format as a number with 2 places after the period (10.25 for example) if you want to track partial days, other format for no zeroes after the period (13)
5. In column E, put this formula:
(D2/30)*1.25 (assuming this is line 2)
You can then copy these lines as many times as you need to, total them, etc.

For Current Date - you can use the =Now() function in your cell where you want the date.

For Contract #, I don't know what you're using, but you can link to a database of contract #s (see below), or you can name a range like current contract #, which gets updated by 1 each time you add another contract, which then is automatically posted on your EXCEL



DGET(database,field,criteria)
Database is the range of cells that makes up the list or database. A database is a list of related data in which rows of related information are records, and columns of data are fields. The first row of the list contains labels for each column.
Field indicates which column is used in the function. Enter the column label enclosed between double quotation marks, such as "Age" or "Yield," or a number (without quotation marks) that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on.
Criteria is the range of cells that contains the conditions that you specify. You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label in which you specify a condition for the column.

Vlookup: =VLOOKUP(A1,A1:C10,3,FALSE) 1. Lookup_value - A1 the value you want to search. 2. Table_Array - A1:C10 You will only search the lookup_value in the left column which is A1:A10 in this example. 3. Col_Index_Number - If you find a matching value give me column 3. Since A1=A1 it will give me C1. In this example 1 is A, 2 is B and 3 is C. 4. Range_lookup - I always choose false which is exact match. True will give you approximate match and its not always correct. Vlookup is used when you have a list of values and you want additional values that exist in other fields. You will get those values only for the fields that you search for. In your example you can get the address by running vlookup at the names, it is also good way to search duplicates. Q2 is very simple, on field D1 just type: =A1 & " " & B1 & " " & C1 Just drag it or double click on the drag square Let me know if you have any other questions Daniel