Match 3 values

Hi all,


On the attached spreadsheet there is a “3G H3” worksheet and a “Location Table” worksheet – plus 2 x “Example” worksheets.

In the “3G H3” tab there are various columns, most of which have been sanitised. I am looking to get the values after the last / in Column G into the relevant cells in the “Location Table” worksheet based on the following:

- Where Column B is not blank and shows a location (i.e. A,B,C,D)
- Where Column J is not blank
- Then copy the unique values from Col G (after the /) in the correct place on “Location Table” worksheet.

For example, as shown in the example worksheets – I have highlighted the relevant columns in “Example Data”. I’ve filtered on Location A and filtered to show the non-blanks in Column J.

For Location A (Column B) in Column G after the last / there are 7 x unique values, that are shown in the “Example Table” worksheet. The data goes into this section as we are interested in Location A, plus the network and technology shown in cell C3, which matches with the values in Column B and C (albeit 3G matches with 3, so the “G” can effectively be ignored”).

Different sheets with show different values in C3, out of a possible of 11:
- 2G EE, 2G O2, 2G Voda
- 3G H3, 3G Voda, 3G O2, 3G EE
- 4G H3, 4G Voda, 4G O2, 4G EE

I am looking to loop through all worksheets, to match those unique values after the last / from Column G in sheets(3) onwards – to match what is shown in the “Location Table” which is sheet(2).

Any ideas?

Not really sure about generation other than 3 though.

Please Login or Register  to view this content.

Fantastic once again Jindon!

This code will work on any worksheet that starts "3G or "2G"...is there a way to reference that?

If the sheet name starts with a number followed by G and a space.

Please Login or Register  to view this content.

Incredible, that is perfect!

One final variable. For sheets titled "4G EE" "4G O2" and "4G Voda" the premise is as follows:

- Non blanks column B (locations) as with above codes
- Non blanks column K NOT column J with above codes
- Instead of taking unique values from after the last / in Column G, need to take unique values from Column K itself

If you understand what I mean?

Thank you once again

Change to

Please Login or Register  to view this content.

I've amended the code slightly to extract the relevant data for 4G EE, as this differs the most from other data.

Currently it looks like this:

Please Login or Register  to view this content.

When looking at the number after the last / from the relevant column, this code need to work with the 2nd / and to replace the last / with a 0 or 2 x 0.

For for example, the data from Column G for 4G EE currently looks like this: LTE 234/30/11858/9

I am interested in the 11858/9 part, so after the 2nd /.

However, IF the number at the end is one digit then the / needs to be replaced with 00 and if the number at the end is 2 two digits then the / needs to be replace with 0

- For example, based on LTE 234/30/11858/9 the number to be extracted to the location table would be 11858009
- Based on LTE 234/30/10644/14 then the number to be extracted to the location table would be 10644014

If you understand what I mean?

Massive thank you once again :-)

You said,

Originally Posted by chrisellis250

One final variable. For sheets titled "4G EE" "4G O2" and "4G Voda" the premise is as follows:

- Non blanks column B (locations) as with above codes
- Non blanks column K NOT column J with above codes
- Instead of taking unique values from after the last / in Column G, need to take unique values from Column K itself

List all the possible combinations in one post.

Apologies Jindon, that one was for 4G O2, 4G Voda and 4G H3.

Been a work in progress, so have come across different variables as I've tested code.

Untested, so if this doesn't work, need to see your wokbook with data and the result,

No many rows, just 10-20 rows will do.

Please Login or Register  to view this content.

Currently getting a "Run-time error '9': Subscript out of range" error on:

Please Login or Register  to view this content.

Any ideas?

Will sort a test workbook for you, just checking that error with you.

That means C1 hasn't got sheet name.

Thanks Jindon!

Code works for 3G and other 4G, but get an error on the 4G EE:

"Run-time error '9': Subscript out of range" error on:

Please Login or Register  to view this content.

I've attached a sample.

No more change in sheets?
Change 2 to 4

Please Login or Register  to view this content.

Sorry, what do you mean change in sheets?

Sheets 1 and 2 will always be "Home" and "Location Table" and then there can be anything between 1 and 11 sheets from sheet 3 onwards, which will consist of any combination of 2G Voda, O2, EE then H3, Voda, EE or O2 on 3G or 4G. So the sheet 4G EE could be anything between sheet index 3 or sheet index 13.

Look at the file in your post #1, row 2 is completely blank, but your last file is not.

Row 2 can be ignored, as what is in that row is irrelevant. The bits in yellow have just been put in to show you what values I am looking for.

For you YES, but not for the code!!!

Right ok, apologies. That can be cleared if necessary.

You are the one who should decide.

If blank on 2nd row, 2 else 4 for starting loop counter.

Ok, sorry I understand what you mean. As the files will always have something in there, then row 2 will have some text in it and not be blank.