Thanks:  0
Likes:  0

# Thread: Identifying number series (Read below for clarity)

1. ## Identifying number series (Read below for clarity)

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

2. ## Re: Identifying number series (Read below for clarity)

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,15 4,15 2,13 2,13 5,23 5,23 1,15 1,15 2,15 2,15 3,14 3,14 =TEXT(B10;"#0,00")

filtered

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

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

3. ## Re: Identifying number series (Read below for clarity)

sijpie.

Thanks for your info

4. ## Re: Identifying number series (Read below for clarity)

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•