Graph Pairs from 3digit numbers sorted to show a trend or sequence

[Brian.Aerojet]

Hello again,

Im trying to sort or show a sequence, trend or something similar in a graph.

Im not sure a graph is the way to go with this. Im up for any suggestions.

In column A is the Date, Column B is the 3 digit numbers, Columns C, D, and E are the single digits for the 3 digit number.

NOTE: Columns B, C, D, E are not needed for the Graph, only A, F, G, and H will be graphed.

Columns F, G, and H are the paired numbers from the 3digit numbers.

Example would be, if I have 762 as 3 digit number, then the pairs from it would be in column "F" 1st and 2nd number(76), column "G" 1st and 3rd number(72) and then column "H" 2nd and 3rd number(62)

OK, now for the part I need help with.

In the 3 columns F, G, and H I want the graph to show the trend of the 3 columns of the paired numbers.

Also, I was told that the numbers that had a zero in front of them like 04, 03, etc would show as 4, and 3 without the zero. If possible I would like the graph to also show the zeros, so when I highlight over any of the numbers it will show me the actual number that is in the data.

I would also like the dates from column A to be part of the graph as well.

Please ask any questions if needed, Im still trying to figure it out myself.

Thanks,
Brian

[Glenn Kennedy]

You need to transform those text numbers into real numbers. =--(C2&D2) or whatever, and format as 00 to maintain the current appearance. Then you can draw charts. I have inserted one. Do you want each point to be an incrmental point on the x-axis, or each date, or each day??

[Brian.Aerojet]

Glenn,

I looked right over top of what you said about format as 00, so when I added the formula to my original excel sheet, it showed the numbers with zero as single numbers.

Sorry I missed what you told me, which got me behind on trying to figure it out myself. All I needed to do was come back and read your post to again, and there it was right in front of me.

Regarding the graph, Im not 100% sure what I want the graph to look like or even a graph at all.

I will try to explain exactly what I would like to see from past history data.

Lets use the (89) in F2 for example in the sheet.

I have many rows with this data in my original excel sheet.

ok the (89) could show around 10 to 25 times on average in column "F" (I do have formulas of countif in place that tell me how many times a number like (89) shows in F column)

What I would like to see easier is where the (89) shows up and the date it showed up on or even pull this data to another area on the worksheet, I just figured a graph may be the idea, but the more I think about it, Im not sure it would be.

I look back to something similar you helped me with about a year ago that pulled data like this. I don't think we had the Date showing in that information before.

Below is an idea to consider if you think it is possible

It would look similar to this: (Each number like (89) or each date in different cells) also note this same thing would happen for (G) AND (H) columns also

(89) 3/21/2018, 3/29/2018, 5/1/2018, 6/14/2018, 7/28/2018, and so on

(42) 4/23/2018, 4/30/2018, 5/1/2018, 7/22/2018, 8/13/2018, and so on

(56) 2/26/2018, 4/2/2018, 6/19/2018, 7/21/2018, 8/6/2018, and so on

[Glenn Kennedy]

OK. Try this. An array formula for the 1/2 digit combos. Returns a unique numerically sorted list:

=IFERROR(SMALL(IF(FREQUENCY($F$2:$F$32,$F$2:$F$32),$F$2:$F$32),ROWS(K$2:K2)),"")

Then this in L2, copied across as far as needed, and down:

=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($F$2:$F$32)/($F$2:$F$32=$K2),COLUMNS($L:L))),"")

IF you want all the dates in ONE cell per row, VBA will be needed. Easy to set up, though.

[Brian.Aerojet]

Im putting it in my excel sheet right now to see how it looks and make sure i have no issue.

As far as VBA goes, i know nothing at all about it, will it be put in the same sheet as I'm using now?

[Glenn Kennedy]

Incidentally, why do you have all those helper columns? They're not needed. Done for digits 1&2...

[Brian.Aerojet]

I like that way better that you just sent in post #6.

The other columns are not helpers, what i did with those is break down the 3 digit number to single digit numbers, after that what you are working on for me is 2 digits of the 3 digit number in all sequences except reversed.

[Brian.Aerojet]

Why do think I would get a blank in cell k2 with this formula you give me

=IFERROR(SMALL(IF(FREQUENCY($F$2:$F$32,$F$2:$F$32),$F$2:$F$32),ROWS(K$2:K2)),"")

I also got same thing with post # 6 worksheet you sent (Blank)

OK, I got it figured out, lets go with the 'Pairs Rethought' sheet you sent me.

In my original sheet I have blank cells in between the 3 digit numbers down column B in that sheet. I made an example to test it by putting 3 digit numbers one after another down the column and it worked, what would I add to the formula to account for these blank cells?

[Glenn Kennedy]

Try this. I will look at the flakey formula in a second.

[Glenn Kennedy]

I don't follow your comments at Post 8, Post the offending sheet.

[Brian.Aerojet]

Read the last of my post on #8, i just updated it after you had already sent the post #9

ok doing now

[Brian.Aerojet]

I just dragged the other numbers out of the way

I will be doing same thing with them as we are doing now, but it will have 3-digit numbers in different columns

The part that stays in same column is the Date

I just copied yours so all formulas are still in place

BTW SORRY for the confusion, I need to be more specific with my uploads, I just assumed I could figure it out if it was not exactly the way my original sheet is.

[Glenn Kennedy]

It's not looking good with the blanks. However, if I re-introduce 2 hideable helper columns, it should be OK.

[Brian.Aerojet]

Thank you Glenn that would be great, Im glad I did not go with the graph, I definitely like this look a lot better

[Glenn Kennedy]

Make that 1 helper column...

CTRL-F3 to view the Named range (Sort). If this is what you need, one or two more named ranges and you'll be good to go.

[Glenn Kennedy]

How many rows MAXIMUM in your real data?

[Brian.Aerojet]

I have a mac computer, CTRL-F3 is not the same, do you know what i hit instead of control F3?

About 20k rows

[Glenn Kennedy]

Google says:

⌘ + fn + F3

[Glenn Kennedy]

You may well need VBA to assemble the unique list(s) of 3 character numbers.... It MIGHT be OK if they were not sorted in numerical order...

[Brian.Aerojet]

I would love to use VBA, but I know nothing about it and plus I don't want to take up all your time.

If you want I can try to upload my original for you to look at and then you can decide what would be best for me.

Im going to need to do something anyway, the workbook is starting to slow down more and more. I need to be in the KB and not MB

[Glenn Kennedy]

Laptop is flat out of juice. Going out for lunch. I'll be back later and will complete the job.

[Brian.Aerojet]

Thanks Glenn, It is 5:30am in East U.S.

I'm going to bed, I will look at what you have when I get up in about 6hrs.

I left off at the Define Name.

[Brian.Aerojet]

Hey Glenn,

I have it working with Dates and in Numerical order just like in your Sheet called "Pairs Rethought (GK2)"

I have only Pair 1 & 2 done

Pair 1 & 3 and Pair 2 & 3 I have not attempted yet. The reason being is, just doing the 1 & 2 has slowed workbook down big time. You have any other suggestions besides deleting some of my other worksheets?

[Glenn Kennedy]

Well. I'm in western France at the moment where lunch is long and somewhat liquid.

Try this....

[Brian.Aerojet]

LOL, Have fun. France would be good right now.

I will be going to Las Vegas in October, that will be good enough for me. I have never been there.

Anyway, I will work on some changes in other areas of the workbook to see what happens over the weekend, as of right now It is taking several minutes to calculate for everything I do with the workbook.

I will go to manual calculations so I can get something done.

[Glenn Kennedy]

Las Vegas is... very different. I've been there once, had a REALLY great time, but will never go back. However, the SW: Nevada, Utah, Arizona, and so on. BRILLIANT. We can't wait to get back. But the UK Pound is a bit feeble at the moment, so not just yet!!

[Brian.Aerojet]

Yes, Vegas is a one time trip for me. We have day trips planned for Hoover Dam and Grand Canyon.

[Brian.Aerojet]

Glenn,

We are going to need something different for my worksheet(s), I had it working good, but when I try to calculate the workbook it takes forever.

Im using the rethought worksheet from post #24 you sent me.

You have any more ideas? Will I need VBA?

[Glenn Kennedy]

Are we overcomplicating things a lot??

You are starting with 20,000 rows of 3 digit numbers (so a lot of repeats). You are winding up with 3 lists of 2 digit numbers running from 00 to 99. A maximum of 100. Are we wasting a lot of processing time generating pointless lists?

So, I created them myself. With 20,000 rows of data and ~ 100 numbers, you can expect about 250 dates per number!!! Take a look at this... The Lower numbers are under-represented because of the way I generated the random lists.

[Brian.Aerojet]

I have some major downsizing to do on this workbook.

Looks like i will need to start over with my original workbook.

You have any other suggestions for me regarding my issue or maybe suggestions on what I should try to keep this workbook size under?

Seems like the more I add to it, the slower things start getting.

[Brian.Aerojet]

Glenn,

Here is my updated original Workbook. Can you look at it and give me some input on how to speed up the calculations when making changes if possible, even if the answer is to split the data between several workbooks. Im hoping I can keep it in one workbook, but do not know if it is possible.

Im going to show this thread as solved and send reputation right after your input.

[Glenn Kennedy]

I have it done. Hslf the size. Much faster. I just can't connect PC to www right now!! Will try later.

[Glenn Kennedy]

A moment of connectivity!!

Select the draw from the yellow dropdown....

[Brian.Aerojet]

Glenn,

Unbelievable, You're the Man.

Works Perfect for me.

Thank you for sticking with me through this issue I had.

Brian

[Glenn Kennedy]

Aww shucks!!! You're welcome. "See" you again, sometime...

[Glenn Kennedy]

Following a PM, a minor tweak. CTRL-F3 to view/edit named ranges. Formula in column F amended and future results (!!) deleted.