Grouping by inconsistant values

[Cram]

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

[Mark@Work]

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.

[Portuga]

Have you considered applying an auto filter to your data?

You could then use the "custom" function of the auto filter in Column E and select "contains" QC

[Cram]

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

[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.

[Portuga]

Hi Cram,

As Mark said, the first step is to

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.

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)

[Cram]

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.