Grouping by inconsistant values

I posted this problem on a similar board that I have found very helpfull before but 2 sites are better than one, eh, cos i think this is very tricky. If anyone suggests a solution I'll ensure both sides are kept up to date to ensure nobody wastes their time.

http://www.mrexcel.com/forum/showthread.php?t=309342


Thanks
Marc

You indicate that you have devised a way to identify if a cell has a valid QC# value in it or not (I assume using FIND or similar).
Use this information to set up a spare column with the row number, using ROW() if it is a QC# or "" if it is not.

Then use index(A:A,SMALL(B:B,ROW()))
Where A is the column containing the QC# values and B is the ROW or "" column.

You might need to add or subtract from the ROW() value to acount for heading lines etc.

Mark.

Thanks for your input guys but I've been thinking about this over night and i think i can explain it better.

On my sheet 'QCs' I have a list of 4 digit numbers (column A) i.e.

5542
4441
5412
5447
etc

On my sheet 'Raw Data' I have rows of data and somewhere in row E there should be a number that matches one from the list of numbers in 'QCs' i.e

A | B | C | D | E
TestDataNon | 12/04/08 | 13.21PM | Joe Bloggs | Set Up Int QC 5542
DataCleanUp | 19/02/08 | 12.21PM | Joe Bloggs | Set Up Int QC 4441


The output from this I need is to display, on a new sheet, groups OF all rows in 'Raw Data' which have the same QC number which will look something like

5542
DataCleanUp 19/02/08 12.21PM Joe Bloggs Set Up Int QC 5542
EditNewClass 11/02/07 19.43PM Joe Bloggs Max No Pa QC 5542
TestDataNon 12/04/08 13.21PM Joe Bloggs Set Up Int QC 5542

4441
DataCleanUp 19/02/08 12.21PM Joe Bloggs Set Up Int QC 4441
EditNewClass 11/02/07 19.43PM Joe Bloggs Max No Pa QC 4441
TestDataNon 12/04/08 13.21PM Joe Bloggs Set Up Int QC 4441

etc


I hope this explains it better. Again, any further help would be fantastic

The First thing to do is to set up a column containing just the number from column E.
Perhaps set Column F to be RIGHT(TRIM(E1),4)
This will trim all spaces first then take the last 4 characters of the string.
This will work for the examples given, if the character strings are less predictable, you may have to be more creative.

You now have to use a combination of MATCH and OFFSET to get a list of all row numbers that match each of your posible 4 digit numbers.

A couple of extra questions:
Will the digits of the QC number always be the last 4 digits in the string?
Will there always be a set number of entries against each QC number?
(your example suggests there will always be 3)

Mark.

Originally Posted by Mark@Work

The First thing to do is to set up a column containing just the number from column E.
Perhaps set Column F to be RIGHT(TRIM(E1),4)
This will trim all spaces first then take the last 4 characters of the string.
This will work for the examples given, if the character strings are less predictable, you may have to be more creative.

You now have to use a combination of MATCH and OFFSET to get a list of all row numbers that match each of your posible 4 digit numbers.

A couple of extra questions:
Will the digits of the QC number always be the last 4 digits in the string?
Will there always be a set number of entries against each QC number?
(your example suggests there will always be 3)

Mark.

Thanks for that. The QC number can apear anywhere in the string and the number of entries against each QC number can vary.

I've never used either MATCH or OFFSET so i'll have a look at those and hopefully the open up a few options.

Originally Posted by Portuga

Hi Cram,

As Mark said, the first step is to


Once you have this, a possible solution is to create a a pivot table with this data.
Good thing is that you can identify duplicates (if the total by row is more than 1.

(See attached)

I've never used Pivot Tables either so thanks for the link. The problem is that this really has to be something I can automate so that the user can simply press a button and this will be produced as part of a larger report.

Five weeks I've been working on the whole thing and every time I think I've finished I'm asked to make it do something else

You're getting to a point where if you want this all automated, you'll need some sophisticated VBA programming. You can use dummy columns with

Please Login or Register  to view this content.

where E1 is the QC you're looking for and D2 is the cell in the column you want to search. You can copy and paste the entire thing and to a new tab and sort by the different columns or move it to a new tab using formulas. It gets tricky. :-/

Attached is an example of my dummy column formulas.

ChemistB

Ah

Thanks for both your inputs. I think I have a good idea of how i can achieve this now. I'll give it a go and report back later with my results (or more help)