How to lookup repeated data?

PERERA

New Member
Joined
Oct 13, 2002
Messages
5
Say I have a large list of data and the left most column (A) has many repetitions as below.

Country¦Project¦ Month¦ USD
AT¦ A¦ 1¦ XXX¦
AT¦ B¦ 6¦ XXX¦
BE¦ C¦ 4¦ XXX¦
UK¦ A¦ 1¦ XXX¦
UK¦ A¦ 2¦ XXX¦
USA¦ A¦ 4¦ XXX¦
USA¦ B¦ 5¦ XXX¦

With out filtering in the same sheet I would like to use a function similar to lookup and get all the data related (summary) to one country. I would like to see all the entries (as if I have used the advance filter) please. Thank you
This message was edited by PERERA on 2002-10-14 11:07
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

ThePencilQueen

Board Regular
Joined
Jun 26, 2002
Messages
109
How about a pivot table with country in the page, project in the rows, month in the columns and sum of dollars in the data.

You could then use the drop down to create a new page for each country and copy and paste to a separate summary sheet.

Of course I could be talking out of my a**e
 

PERERA

New Member
Joined
Oct 13, 2002
Messages
5
Thank & sure all what you said will work. But I want to publish the data in the web with no knowledge about HTML or that sort (I have XP, perhaps using FrntPage2002) and it seems that active spreadsheets do not support pivots or filters but will support formulas. So I would like to have one sheet as kind of a template (series of formulas) then source sheet hidden with all the data. Formulas may be liked to a validation list to act as a variable!! Does it sound complicated or do you need some more information?
 

ThePencilQueen

Board Regular
Joined
Jun 26, 2002
Messages
109
It sounds VERY complicated - have you looked into importing into access? Access has some very nice user friendly ways to put data onto the web without getting too tied up in html?
 

PERERA

New Member
Joined
Oct 13, 2002
Messages
5

ADVERTISEMENT

In fact data is extracted from Access. I did not looked in to the possibility of publishing directly from there. I assumed it would be difficult! Good if it is easy and does not need coding I will go for that option. Could you direct me to any good sites that I can get some info on this please?
 

ThePencilQueen

Board Regular
Joined
Jun 26, 2002
Messages
109
The best place I've found is in the Access help files - try searching for statis and dynamic format (static involves uploading your spreadsheet/data as is, dynamic allows it to be updated/added to once it is online)

Then it is just a case of creating a query/report showing the data you want and exporting it as html files (File|Save as HTML| follow the wizard)
 

chef

Well-known Member
Joined
Jul 10, 2002
Messages
566
Office Version
  1. 365
  2. 2016
have done this a few times myself...prob is trying to explain solution in a couple of replies..

i am off on holiday just now but if you want to private message me with an example of your worksheet i could probably help you out..i am back to work next monday

next...you dont really need to know html to publish your work and i can talk you through how to hide your main sheet so that no user would see.

regards
chef
 

Forum statistics

Threads
1,144,217
Messages
5,723,063
Members
422,477
Latest member
pete101

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top