Consolidating set of Numbers series

[Ahmed]

I often receive set of numbers series (from numbers to to numbers), which needs to be consolidated. For example:

This is the raw data I receive:

From To
2021454667 2021454866
3021841686 3021841935
3021841936 3021842435
3021842436 3021842648
3021850649 3021850935
3021850936 3021851035
3021851036 3021851285
3021851286 3021851385

When consolidated it will be:

From To
2021454667 2021454866
3021841686 3021842648
3021850649 3021851385

Please find attached the excel sheet and check all the three sheets, rawdata (as I receive), WorkingSheet (How I work it), Result (final output file). In the Working Sheet I have explain what is required and how I do it.

I hope I have explained the problem clearly. I need a solution with some simple VBA or through formula.

Note: I receive data which runs into 10K rows and more...

Thank you in advance.

Syed Ahmed

 

[Abdul Matheen]

Hi Syed,

Please find the attached workbook with VBA code for your query.

Run the macro in the file, and results will appear in the result sheet.


Regards
Abdul Matheen

 

[SirJB7]

Hi, Ahmed!

Give a look at the uploaded file. It uses a helper column C, and the output is in columns E:F. And a bit tricky row 1.

Formulas:

A1: 0, formatted as "From"
B1: 0, formatted as "To"
C1: 0, formatted as "Sequences"
E1: From
F1: To

C2: =SI(A2=B1+1;C1;C1+1) -----> in english: =IF(A2=B1+1,C1,C1+1)
E2: =INDICE(A:A;COINCIDIR(FILA()-1;$C:$C;0)) -----> in english: =INDEX(A:A,MATCH(ROW()-1,$C:$C,0))

Copy across E2 to F2; copy down C2:F2 as required.

Just advise if any issue.

Regards!

 

[Ahmed]

Resolution came very fast. Hence this quick acknowledgment. Thank you Abdul Matheen and SirJB7.

After going through the solution and testing in a couple of cases, I will provide the feed back.

Thank you both, once again.

 

[Ahmed]

1. The VBA provided by Abdul Matheen worked well. Now I can tweak to my needs. Simple, easy to understand code. This really help resolve my problem. Now I can work in these excel faster.
2. SirJB7 solution looks even more simpler, without the need of VBA / Macro. But unfortunately, it did not work the way I wanted. But still I would like to thank SirJB7 for giving me an idea that without VBA this can be done. I am trying to take the lead from his ideas to get the desired results. Once done, I will post here.

Thank you forum and both Abdul Mathen & SirJB7.

 

[SirJB7]

Hi, Ahmed!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!
PS: If you tell me what didn't worked as you expected maybe I can fix it, so as you can have both a macro and formula solutions.

 

[Ahmed]

Sri SirJB7: I am glad you coming forward the fix the problem. Yesterday I did try, but in vain.
Macro is working well - and using it now. But I prefer formula solution rather than macro.

My problem was:

From To Sequences
2021454667 2021454866 1
3021841686 3021841935 2
3021841936 3021842435 2
3021842436 3021842648 2
3021850649 3021850935 3
3021850936 3021851035 3
3021851036 3021851285 3
3021851286 3021851385 3

Using your suggested formula, the result is:

From To
2021454667 2021454866
3021841686 3021841935
3021850649 3021850935
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A

The actual result is:

From To
2021454667 2021454866
3021841686 3021842648
3021850649 3021851385

See the difference of BOLD TO serial no. Somewhere I am missing something. If you can identify, then I will be very glad and very very HAPPY.

Upload your same sheet adding actual result in column H:I
Thank you in advance...

 

[NARAYANK991]

Hi Ahmed ,

Something like this ?

Narayan

 

[Ahmed]

Yes, NARAYAN!

Today I had my wow moment.
Now I understand why everyone become awesome here!

Kudos!
Syed Ahmed...

 

[SirJB7]

Hi, Ahmed!
Give a look at the uploaded file. It's fixed and I think it works fine now. Changed formulas as follows, including condition for avoiding N/A error:
E2: =SI(FILA()-1<=MAX(C:C);INDICE(A:A;COINCIDIR(FILA()-1;C:C;0));"") -----> in english: =IF(ROW()-1<=MAX(C:C),INDEX(A:A,MATCH(ROW()-1,C:C,0)),"")
F2: =SI(E2<>"";INDICE(B:B;COINCIDIR(FILA()-0,1;C:C;1));"") -----> in english: =IF(E2<>"",INDEX(B:B,MATCH(ROW()-0.1,C:C,1)),"")
Regards!

 

[Ahmed]

Yes, now it is working the way I wanted.
Thank you SirJB7...

 

[SirJB7]

Hi, Ahmed!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!