PDA

View Full Version : Impossible Task Maybe ??!!



fireslguk
01-14-2006, 07:24 AM
Hi there all,

i am stumped on this one. !!!!!

A user enters a company name in sheet 1 column b

obviously as time goes on there are quite a few companys that are listed.

We do not know the company name in advance until they call.

My Query :


I want sheet 2 to automatically display the company name in one cell, the next cell = the number of times they are listed in sheet 1.

I then would like the whole lot in order so the company that is listed the most at top working down.

Rather than manually sort the list, then do a count if formula which would need doing everyday (because we dont know the company name until they have called) is there another way ? http://www.mrexcel.com/board2/images/smiles/icon_eek.gif

thanks any1 if you have a solution. :banghead:

mdmackillop
01-14-2006, 07:37 AM
Hi,
Welcome to VBAX.
Something like this?
Regards
MD
http://vbaexpress.com/kb/getarticle.php?kb_id=705

mdmackillop
01-14-2006, 07:58 AM
There are a few approaches to this. Why are you adding a previous name to a list, instead of a count next to an existing name?. This is very prone to errors unless you are using data validation. If you could explain the purpose of your question, I'm sure a better solution could be found, than displaying data on another sheet.

fireslguk
01-14-2006, 08:23 AM
thanks md mack,

i understand that if a user enters a company name differently then itll count differently so i will make sure accurate input. the name maybe added again because the sheet 1 has rows of data added daily and keeps going down. a user wont look at the rows of data to see if the company called before. the sheet1 would contain about 600 rows for a years worth. as i said in my first post the companys calling are not known until they call and i wanted another sheet to display the results automatically in highest no first not alphabetically, hope a little clearer bud

fireslguk
01-14-2006, 09:25 AM
ok did the duplist thingy and added another module to carry out the other required task.. trouble is gets stuck on the paste part. macro stops at this point. what am i doing wrong


Sub Button4_Click()
'
' Button4_Click Macro
' Macro recorded 14/1/2006 by Sean
'

'
Selection.AutoFilter Field:=2, Criteria1:="afa"
Columns("B:B").Select
Selection.Copy
Selection.AutoFilter Field:=2
ActiveCell.SpecialCells(xlLastCell).Select
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Application.Run "afa.xls!DupList"
Columns("A:B").Select
Selection.Sort Key1:=Range("B1"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("B1").Select
Selection.ClearContents
Range("A2").Select
End Sub

mdmackillop
01-14-2006, 11:31 AM
I had a UniqueList form to which I've added some code to do your sort. Because the unique data was already there, it's a bit simpler.

fireslguk
01-14-2006, 01:33 PM
mmmm okedoke, its getting closer some is working

have you limited the data in sheet 2 to b3 - b 18 ?

i opened your file, selected the sample data and replaced with 1 to 20 (which your sample data went up to b18). i clicked on add name and filter button which gave me user form and clicked on down arrow 17 18 19 20 where missing and missing on sheet 2

mdmackillop
01-14-2006, 01:51 PM
Hi,
The userform is based on a data range named Firms, adjust this to suit your data. once set, new items will be inclused in the named range when entered by the combo.

Alternatively, the following change to the Initialize routine will make the range dynamic
'Set Range Name to suit
Set DataList = Range(Cells(3, 2), Cells(Cells.Rows.Count, 2).End(xlUp))

fireslguk
01-14-2006, 03:43 PM
hi mdmack, i decided to use a pivot table instead, gave me the results i needed, hey thanks anyway dude !

mdmackillop
01-14-2006, 04:39 PM
No problem,
Happy you found a solution.