Identifying number series (Read below for clarity)

[panyagak]

Hi Fellow MrExcelian Experts.

I have A problem I will try to be clear:
Its simplying my final data analysis to replace the Tedious VISUAL IDENTIFICATION of Number Series

My final data analysis table sorted in Order of Colns B & AG (Critical colns of Interest) is close to this one Example.

My data table start from Column A to AG: Coln A is Date, Coln B Subject Names, Coln C is Blank, Colns D to AG are values & MORE IMPORTANTLY COLNs G & B.

The difficulty I have is matching any combination of varied numbers rounded to 2d.p. in COLUMN AG IN SEQUENCE Eg. assuming in coln AG for one Subject Name is as follows vertically; (1.27, 4.15, 6.13, 3.12, 1.05, 2.15, 2.11, 3.15), I need to pick out in ascending order no.s (2.15, 3.15, 4.15). My problem is: Is there a formula or VBA code to apply concurrently to Coln B & AG to help "Smoke out" the series from Eg. above

Any appropriately different approach is welcome.

Regards with Thanks
Patrick


EDIT:
Missing vital info: The table is a result of consolidating 2 sheets. Each sheet filled with 2 different colours.

Data after sorting by Coln B & AG in each sheet is identifiable by 2 distinct colours eg. Blue for one. & Yellow for the other hence each Coln B (Subject Name).

ONE CRITICAL CONDITION IS:
ONLY 2 numbers FOLLOWING EACH OTHER MUST COME FROM ONE COLOUR. Eg from Blue 2.15, 3.15 & from Yellow 4.15 & Vice versa is perfect.

Its clear now

 

[sijpie]

Hi Patrick,

your required output is not fully clear yet. Anyway to get you started, can you not use some filtering or a pivottable to obtain your result?

with filters you can do a lot. I the small example below I have added a column where I have the numbers from column AG as text. i can then filter them using custom filter: is equal to ?.15

unfiltered
[TABLE="class: grid, width: 187"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]data
[/TD]
[TD]dataT[/TD]
[/TR]
[TR]
[TD="align: right"]4,15[/TD]
[TD]4,15[/TD]
[/TR]
[TR]
[TD="align: right"]2,13[/TD]
[TD]2,13[/TD]
[/TR]
[TR]
[TD="align: right"]5,23[/TD]
[TD]5,23[/TD]
[/TR]
[TR]
[TD="align: right"]1,15[/TD]
[TD]1,15[/TD]
[/TR]
[TR]
[TD="align: right"]2,15[/TD]
[TD]2,15[/TD]
[/TR]
[TR]
[TD="align: right"]3,14[/TD]
[TD]3,14[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]=TEXT(B10;"#0,00")[/TD]
[/TR]
</tbody>[/TABLE]

filtered

[TABLE="class: grid, width: 187"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]data
[/TD]
[TD]dataT[/TD]
[/TR]
[TR]
[TD="align: right"]4,15[/TD]
[TD]4,15
[/TD]
[/TR]
[TR]
[TD="align: right"]1,15[/TD]
[TD]1,15[/TD]
[/TR]
[TR]
[TD="align: right"]2,15[/TD]
[TD]2,15[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]=TEXT(B10;"#0,00")[/TD]
[/TR]
</tbody>[/TABLE]


I know it is not the full solution you are after, but it will help you speed up your manual process.

I think what you are after can only be done through VBA

 

[panyagak]

sijpie.

Thanks for your info

 

[panyagak]

There are 4 sheets stored somewhere from where to Loop & identify Expected numbers which surely are in either of the 4 sheets, though not a must: they're few matchesIn other words, if in Coln AG upon preliminary analysis is as follows:1.832.83(Expected search no. is 3.83: <0 is not needed6.385.38Expected numbers here are: 4.38 or 7.384.315.31Expected numbers here are: 3.31 or 6.31EtcThe table format remains as above:MATCH approach is possible but clueless...Thanks