Identifying number series (Read below for clarity)

panyagak

Board Regular
Joined
Feb 24, 2017
Messages
236
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
 
Last edited by a moderator:

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,610
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
data
dataT
4,154,15
2,132,13
5,235,23
1,151,15
2,152,15
3,143,14
=TEXT(B10;"#0,00")

<colgroup><col><col></colgroup><tbody>
</tbody>

filtered

data
dataT
4,154,15
1,151,15
2,152,15
=TEXT(B10;"#0,00")

<colgroup><col><col></colgroup><tbody>
</tbody>


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

Board Regular
Joined
Feb 24, 2017
Messages
236
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
 

Watch MrExcel Video

Forum statistics

Threads
1,096,246
Messages
5,449,232
Members
405,559
Latest member
Chelseytx

This Week's Hot Topics

Top