lookup Col and display in order

Seeking your assistance again please
Attached workbook example tried to explain what I am seeking to do
1. Using references in a Column in various zones I am seeking to be able to show then print the list minus the blanks
2. There will be occasions where the same material is used more than one [it still should show on the order of the column

As always assistance appreciated

Please Login or Register  to view this content.

Thanks
Tried the code
ended up with only 1 in each cell from T21 to T98
Needed a list or order of the SPXX starting from P37 as per the example

Appreciate you assistance

The site is very slow-almost dead-will check in in 2 hours time.

Thanks for that.
We are almost there with this
The Info shown needs only to be the info with SP Numbers [There will be other info in column E]
and the Titles "Zone 1, 2 and 3" to go into Col O with the SP Number into Col P
Sorry I was not clear before

Assistance appreciated

I do not know what other data you have with SP. You need to show me the actual data in that column. I have assumed they are 2 alpha letters and numbers.

A quick look at my code would have shown that I made 2 spelling mistakes (using iPad without Excel access) which caused it not to work properly.

Please Login or Register  to view this content.

rather than setting the For xRow and having to know the number of rows, it may be better to simply put "End" in the zone column and then change the For Next loop to a Do until (zone = "End")

AB33
So Close
There are other things in Col E like Live, Nett, New but mostly blanks
The code needs to look at SP00 to SP610
Two alphas and up to 3 numeric
There are also 8 zones I have only put In 3 in the hope that I will be able to expand your code
We are so close here

Many thanks for the code and assistance appreciated

I have included the words-like Live, Nett, New on the sample for testing.

Thanks I only want the SP numbers to show
I mentioned there are 8 zones
Could you assist with the expansion of the code to accommodate this

Zone 1 Col F 3-169
2 - F175-259
3 - 265-348
4 - 354-405
5 - 411-491
6 - 497-554
7- 561-613
8 - 619=699

and sorry the col is F not E

appreciate your assistance

Also how can I alter the Macro to apply to current worksheet - not just sheet 1 - this will be copied

No, I am not going to help you until you attach a sample which mimics your actual data.

Hope this helps to explain
Assistance appreciated

Zones refer to times durinf a 24 hour period
Zone 1 is from Midnight to 6am
Zone 2 is 6am to 9am and so on
Col e represents material that is used during that time zone
No all cells have material indicated
I only want to see the SP Numbers

What to the code objective is - is to List the SP numbers from Col E as you have provided in Col P
This would be printed and given to the operator to prepare the material in order for that day.

your 4.49am post with example was near perfect except the extra time zones were not there in my example and it applied to sheet 1 rather than any sheet the macro is in

Trust this clarifies

Should read "No all cells have material indicated"

Try again "Not all cells have material indicated"

You have not answered any of my request. You expect me to act like a mind-reader- unfortunately, I am not.
The reason why my code worked that it has Z's in column C. The zone numbers were extracted from Z1,Z2 and so on.
I have amended the code to work on active sheet. Your cursor has to be on the sheet you want it before you run it.
I have added some zones on sheet1 and see the outcome. As I said if this does not work, you need to clearly show me where to start and the final out put.

AB33
Sheet one on the 6.11am is just what I am after.
It is perfect except there are only 5 zones
I don't understand your code [lack of knowledge on my behalf] so I cannot add the extra 3 zones
I would like to expose the full workbook but I cannot for privacy reasons and I apologies if I am not communicating well

I would send the workbook to you privately for assistance

I really appreciate the help and I get frustrated at what I do not know

No-one has asked you to reveal your actual data, but the sample data should mimic it. For e.g, you want the data to be in column F, yet your sample shows in column E. You did not mention there are other data in column E. You only include SPs.
On your last sample, you include Z in column D while on the first file it was on column C and go on. What you have been asked was not difficult. Remove all the confidential information from the sample, but the sample should resemble your actual data, so that the code will be easily applied to your actual with out being seen by an outsider.
I used Z1,Z2 and Z3 which appear on column C to extract the zone numbers. If your data are consistent and have the same lay out as the first sample attached, the code should work, but if your Zs are all over place, it will not work.
I suspect the reason for not getting other zones is that you do not have Z5-Z8 in column C. Am I right?

Okay - I trust I can get this example right
The attached sheet has my Layout as correct the Cols and positions are correct
I have indicated what is in the other columns
I have also indicated the zone references e.g. Zone 1 Col F5 to F 169 and so on
Do not need the sheet nominated because it is copied e.g. The main templates are Mon, Tue Wed and so on.
A date is added to create a copy of the template etc
The end result of what you are creating is for me to be able to print a list of SP number in order as per the activity in Col f

Thanks for your patience
The Zones indicated in Col Q - row 8 are for information purposes only - to let you know the range of each zone
For example Zone 1 Col F starting in Row 5 and finishing at row 169 and so on
These rows are static and will not alter
I just put them in The Grey square for reference only

Now the SP+Numbers [e.g. SP67, SP101] only will appear in Col F - They will only appear within the Zone ranges indicated'
The SP Numbers are numbers to identify material needed

The Z1 Z2 etc were to represent the start of Each zone which I had placed in the example only as a guide to where each Zone commenced
I removed them in the last example because I thought they may have been causing confusion
I have now put them back in the exact spots they will be on my actual sheet
I did not realise that your code was using them for what appears in Row P
They are/will be positioned as follows
Zone 1 - E4
Zone 2 - E174
Zone 3 - E263
Zone 4 - E353
Zone 5 - E410
Zone 6 - E496
Zone 7 - E560
Zone 8 - E618

You have been more than patient with me on this and it is appreciated

Zone 1 - has an ID only in E4 - The SP's are in the Range F5 to F169
Zone 2 - has an ID Only in E174 - The SP's are in the range F175 to F259
Zone 3 - has an ID only in E264 - The SP's are in the Range F265 to F348
Zone 4 - Has the ID only in E353 - The SP's are in the range F354 to F405
Zone 5 - Has the ID only in E410 - The SP's are in the range F411 to F491
Zone 6 - Has the ID only in E496 - The SP's are in the range F497 to F554
Zone 7 - Has the ID only in E560 - The SP's are in the range F561 to F613
Zone 8 - Has the ID only in E618 - The SP's are in the range F619 to F698

The Id is only there because I thought you needed a reference for the titles in which appear in Col P [Zone 1, 2 etc.]

I have highlighted the zones in Yellow on the attached sheet

Hope this helps clarify

The SP No's in Col Q come from the Zone ranges refer to the attached

The arrows were a bit off in the last example

There is no need to know where the zones are found as they are included in E. For e.g I do not need to know where Zone-3 is found, the code find it self in E-263.
I can also use fixed range for each zone, but, no need so long as where the zone starts, it has to have a word "Zone" in column E, but if the word "Zone is no there, I can change the code and use the zone range instead.

Try the attached.

Many thanks.
You have given me exactly what I was after
Sorry for my lack of communication and thanks for your patience
If you have time I would appreciate following the code process.
I have a basic knowledge but wish to learn more and save asking stupid questions
I just like to be able to look at the code and follow the path

Finally I really appreciate your time and consistence with this post

Regards