• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extracting multiple entries

Busymanjohn

Busymanjohn

Member
Hi, A colleague has asked me if there is a way, in Excel 2003, to extract data from a large report that has multiple entries .... i.e. show the order number and other relevant data for each line. I had initially used INDEX and MATCH in conjunction with the SMALL function in one tab, while in the tab with the large report I added a column to the left of the data and used an IF statement to indicate the ROW number where the criteria met a certain order number ,,,, but my colleague wants to be able to extract ALL order numbers and relevant data where there are multiple entries. I realise that this may involve some VBA rather than any formula function, but willing to try anything.


Thanks
 
Can you expand your IF function to include the multiple criteria? Maybe some examples would help...
 
Hey Luke ,,,, I've tried a few things, including using a Pivot Table, but the report is so large pivots can only cope with a portion of the data. I can post a sample of the data if you wish, just let me know where to post ,,, Rapid Share etc etc.
 
Busymanjohn

Have a look at Using Advanced Filter with a Criteria Range

A Criteria Range is a List of the Data Areas Field Names, with what criteria you need directly under them

eg:

[pre]
Code: 
Date		State	Name
1/04/2010	Texas	Smith*
Ohio
[/pre]
 
Hi Hui, Not sure that would work, the order numbers are numerous and are random throughout the report. The criteria I have is to show the ship status, order number, product and qty. Ship status contains various stages, build, shipped, on order and In Mfg.
 
Busymanjohn,


For help in setting up advanced criteria, you might check out Debra's site:

http://www.contextures.com/xladvfilter02.html
 
Hi guys ,,,, Had a look at Advanced Filter and it's a good tool to have,however I am either doing something wrong or it's not quite what I am looking for. Yes I can filter the report to show the relevant data for specific order numbers, but the user will not know what those order numbers are that appear numerous times in the report, i obviously can't use a unique separator as this will include all order numbers even when they appear only once. Is there a way to filter or extract the order numbers from the report if they appear more than once in the report which would then allow me to pull the much needed relevant data?
 
Busymanjohn


Using your sample file as an example try the following


Setup Titles

Copy A1:E1 to P1:T1

Copy C1 to J1


Set Search Criteria

Type 1234567 into J2


Filter

Goto the Data, Advanced Filter tab

Select Copy to Another Location

List range: $A$1:$E$43

Criteria range: $J$1:$J$2

Copy to: $P$1:$T$1

Don't Select Unique records Only


Ok


You can remove data by removing the header rows from P1:T1 as required


You can add criteria by adding extra Order Numbers below 1234567

or by adding say Ship Status to K1 and then Shipped to K2 and change the Criteria Range


Enjoy
 
Hi Hui, thanks for the reply, I don't think this is going to help me solve the problem. The solution of using the advanced filter relies on knowing the criteria, in this case knowing the order numbers which appear more than once, however, the user will NOT know which order numbers appear more than once ( the report has over 7000 rows ). Do you know of a way to pull data from a large report where the order number appears more than once without actually knowing what those order numbers are? I have looked through a few different web sites and books and can't find anything that suits, perhaps this is only possible using VBA ??
 
You can use an Advanced Filter on just that Column and extract a Unique List of the order numbers.
 
Hi Hui, Yeah, but a unique list of the order numbers will include those which only appear once as well as those which appear multiple times ..... there must be a way to do this, but perhaps it can only be done with VBA code ( which I am not good at ).
 
Once you have the list of order numbers, could you run a quick set of:

=COUNTIF(MainList,OrderNumber)

and filter/sort that on everything greater than 1 to get the list of multiple orders?
 
Bear with us Busyman, We're going to get this one, one way or another

Staying with Advanced Filter


Add a Column next to your data, with a Title called Dupe

Add a formula to the First Row below Dupe (Row 2) =COUNTIF($B$2:$B$15,B2)>1

adjust the Rows and Columns to suit your Column you want duplicates from

Copy down


Advanced Filters


Setup Titles

Copy A1:F1 to P1:U1


Set Search Criteria

J1: Dupe

J2: True


Filter

Goto the Data, Advanced Filter tab

Select Copy to Another Location

List range: $A$1:$F$43

Criteria range: $J$1:$J$2

Copy to: $P$1:$U$1

Select Unique records Only


Ok
 
BusymanJohn

Do you have access to Excel 2007 or 2010?

I found a way to automate this but its not available in Excel 97-2003

It uses a Pivot Table, Count Field and Filter>1 occurrence,

Tied together with a Named Range, meaning you can use it in a Drop Dow or for Validation
 
Hi Hui ,,,, I don't have Excel 2007 or 2010 ( yet ), neither does the user of this report unfortunately, but feel free to pass on the solution you found with the better versions of Excel. However, your solution using Dupe with Advanced filter seems to work, I have tested it on the example file and on the real file ( with over 7500 rows ) and the real file now shows 6200 rows, so it looks to have worked ,,, in fact the Dupe looks to have also excluded those rows that had no order number ,, i.e. they were blank, so that was an added bonus. Thanks again Hui for sticking with this, I know you love a challenge..... all I need now is a solution to my email problem using Excel.
 
Busymanjohn

I have dragged out an old technique I used yonks ago but its really useful

It involves the following


1. Add a field Count which counts the number of times an Order No appears

2. Setup a Dynamic Named Range over your data area "Data"

3. Insert a Pivot table and put Order No in Rows and Count of Count as a value Field

4. Remove Grand Totals

6. Add a named Range which retrieves the Order No's "List"

7. Filter the count field so that 1 doesn't appear (vba)

8. Add a small VBA code to auto update the Pivot Table when the data changes (vba)

9. Use List as a Validation List

10. I'm sure there is more to it than the few steps above, but that will get you going


I have used your test data as an example (All in Excel 2003)

https://rapidshare.com/files/459775019/Return_Mulitple_Entries_-_Hui2.xls


Enjoy


ps: Also shrunk your sample file from 6.5Mb to 85kb
 
You must log in or register to reply here.
Back
Top