Solved

split a list into a list with the single entry items, and another with multiple entry items in excel

Posted on 2011-02-19
5
808 Views
Last Modified: 2012-05-11
I use the filters on a regular basis in excel but this filter criteria is not a usual one. I am however using for this project excel 2003
I have an alphabetical list (A, B, C etc) that represents group of the same objects in a table. I would like to graph the single items groups in one colour and the multiple groups in another colour. For that purpose, I would like to make 2 new tables, one for the single item groups and the other for the multiple groups. I then need to filter the list for single itemed groups, and place that in the appropriate new table, then filter for the multiple itemed groups, and placed that filtered list in the other new table. From there I can run the vlookups and other formulae that I already have in place.
I somehow feel there will be a simple way to get about it, and have searched but without much results! I think pivot tables might do the trick but these tables are embedded in specific worksheets ( about 20 each), and the order needs to remain as is as it is a multiple user workbook!
Thanks you again! I have learned with this projects trick that I know I will definitely use again much much more!
Danièle
0
Comment
Question by:daniques
5 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 34935084
daniques,

It would be useful to see some sample data and/or a sample file.  EE now allows you to directly upload files to your question.

Please be advised that once you upload a file, it can be publicly accessed, and that it may not be possible to fully and permanently delete it.  The file may also be indexed by the major search engines.

Therefore, be very careful about posting proprietary, confidential, or other sensitive information.  If necessary, use "fake" and/or obfuscated data in your sample.

Please note that at present EE restricts uploads to certain file types.  If your file type does not match those in the list, you can use http://www.ee-stuff.com instead, which is not officially an EE site, but is run by people connected to EE.

Patrick
0
 
LVL 5

Accepted Solution

by:
roger_karam earned 500 total points
ID: 34935089
Hello Danièle,

Have you tried creating a new column with the sum of each item and filtering from that? Ex. If the items are in column A, insert a column B with the formula in B2  "=countif(A:A;A2)" and copy the formula all the way down, then you can filter on column B anything >1.... I think that would be the easiest way and you can hide the column as well...

Is that the kind of thing you are looking for?

RK
0
 
LVL 2

Expert Comment

by:mscanlon06851
ID: 34935512
I agree with matthewspatrick, it's very difficult to visualize what you are looking for from what you describe.  If you could dummy up a sample that shows the way the data looks at the start and the how the same data would appear in the two new tables, we'd be in a much better position to assist you.  

Thanks.

MS
0
 

Author Closing Comment

by:daniques
ID: 34935974
Never thought of using a dummy column where entries are either 1 or >1. That did the trick very well, and I could simply filter and transpose the results in or or another column depending on the two criteria of >1 or =1.
Thanks!
0
 
LVL 5

Expert Comment

by:roger_karam
ID: 34939678
Thanks for the points!
-RK
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now