List Manipulation

[DanITD]

Hi All,

At work we have Excel timesheets that our contractors fill out so that we can bill clients for our services.

In one of the columns the technician must enter the name of the client for each line of activity. There is also a summary page that when you enter that same customer name, it automatically looks up the time and totals it for display.

The thing is that the customer names on the summary sheet must be manually entered. I was wondering if there was anyway that this could be done automatically. So for example, a formula would look up the entire customer row on the time sheet and only produce a single cell result for each of the different customer names, even if that customer name has been entered many times on the timesheet.

I would appreciate potential solutions.

[teylyn]

Hi,

the technique required to create a contiguous list of unique names from a list with duplicates is quite advanced. It can be done, but you probably don't want to go there. Much easier would be to set up and maintain a list of valid customer names on a separate sheet and then set up data validation to refer to that list.

If you want to know how to do that, please post a small (!) data sample, so the solution can be tailored to your needs.

to upload a file click "go advanced" below and then the paper clip icon.

hth

[DanITD]

Thank you for your response, sounds like a solution that will do what I want just fine.

I've attached a cut down version of an example for you to work on.

Looking forward to seeing the result.

[JBeaucaire]

Maybe not so complicated, you can use a Filter on your Activity sheet, then copy over to the Summary in a couple of easy steps.

1) On Activity, select all the values in column D from D5 to the bottom of the data.
2) Select DATA > FILTER > ADVANCED FILTER > Filter in-place > [x] Unique records only....then OK
3) Copy the visible customer codes and paste them into the SUMMARY sheet at A7.
4) Select DATA > FILTER > SHOW ALL to get the regular list back on the ACTIVITY sheet

[JBeaucaire]

Seeing how you seem to like the array formulas, here's a way to make it truly automatic.

1) In the SUMMARY sheet, put this array formula in A7:
=INDEX(Activity!$D$6:$D$50,MATCH(0,COUNTIF(Activity!$D$6:$D$50,"<"&Activity!$D$6:$D$50),0))

2) In A8, enter this array formula:
=IF(COUNTIF(Activity!$D$6:$D$50,">"&A7), INDEX(Activity!$D$6:$D$50, MATCH(COUNTIF(Activity!$D$6:$D$50,"<="&A7), COUNTIF(Activity!$D$6:$D$50,"<"&Activity!$D$6:$D$50),0)),"")

3) Copy A7 down to A8:A27

4) I noticed a lot of your other array formulas are going down 1000 rows, make sure you really need that much calc going on each cell. Array formulas can really impact sheet performance. Keep that in mind.

[JBeaucaire]

I my opinion, the easiest of ALL would be to manually enter all your customer codes into A7:A27. This would serve to standardize this report.

Also, this list of "codes" could be given a named range and used as a data validation dropdown selection box in the ACTIVITY sheet, making it easy for the column D entries to be correct.

[JBeaucaire]

Dan you had followup questions? Please post them here with an updated example sheet. We don't use private messages that way.

Be sure to read the Forum Rules, note #4...

[DanITD]

Hi JBeaucaire,

I wondered why I didn't get a reply to my post. I just dug up this thread again as I'd really like my question answered.

I've used your array technique in post on 12-04-2009 06:02 AM. It works beautifully.

I've used the same formula not only for our client acronyms, but also for the budget items as these can vary. We do have a set of budget items that I'd like to always be displayed at the top, and the custom ones to go down below. Is this possible? Can you add something to the code below to say 'display these entries at the top, and only at the top'?

For example, we always use 'Time and Materials, After Hours 1, After Hours 2, Management etc.

Hope you can help.

[JBeaucaire]

You have an updated sample sheet with your current layout being used and demonstrating what is new that you'd like to add?

[DanITD]

I've attached the latest file. Apparently .xlt files are not accepted by the upload manager, would be nice if you could do so.

[teylyn]

you can simply rename the xlt to xls and it will upload fine. after you get it back, rename it to xlt again.

[DanITD]

Not sure by your response whether you saw that i did upload the file in zip format. Are you able to unzip it?

[JBeaucaire]

I don't know what you're asking, you posted a completely empty sample layout. I found the Time and Materials table. What about it? Please be exact.. phrases like "at the top" are too vague. Specify cells and perhaps show samples.

Are you talking about a drop down in one of those columns?

[DanITD]

Apologies for my unclear request.

I've entered some sample data into the template, and it is attached.

Currently, anything entered on the Activity sheet, is auto-populated on the Summary sheet.

Column F on the summary page auto-populates the different budget items (Column I on the Activity sheet). What I would like to happen, is for there to be a static few that always sit at the top starting at cell F7 on the summary sheet. What I want is every budget item on the Rates worksheet (the third worksheet) to always show at the beginning. These are budget items that are commonly used. Anything else, such as 'Project - New Server' is non-standard and can just auto-populate alphabetically as it is currently doing.

Let me know if left out any gaps in my information.

Thanks in advance.