• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to go every nth column value.

P

premjeetgupta

Member
Hi Seniors,

I am wondering for a formula that gives every nth column value in down the line row.
like below cell reference

B2 = B1
B3 = U1 (19th Cell)
B4 = AN1 (19th Cell)
B5 = BG1 (19th Cell)

Thanks,
Premjeet
 
Hi Nayarnk Sir,

You formula worked indeed very good, Seems there is some issue with my excel data. Can you check & help me to get data in desired format. I need in first row details which contains "testing pss node" value break from there and a new column starts from down rows...

Attaching file for your refrence...


Thanks you again..
 

Attachments

  • Metro_MTU_Check (2).xlsb
    27.6 KB · Views: 0
Not a formula solution, but if you just need all the "testing" cells, I'd copy the used range, create a new sheet, and do a paste special - Value + Transpose. Then filter col A for everything with "Testing" in it. This lets you get all 178 records pretty quick.
 

Attachments

  • Metro_MTU_Check LM.xlsb
    46.7 KB · Views: 0
Hi Premjeet ,

I think you should go with Luke's solution , since there is a problem with the data layout in your file ; the difference between the columns with the same headers is not constant ; initially it is 19 columns apart , but in between it changes to 21 columns , then reverts back to 19 ; this kind of variation cannot be taken care of by a formula.

Narayan
 
Thanks Luke, For your support but my requirement is not only "testing" cells. I need result in table format as attached "Sheet 2" is for your reference. Testing cells just shows from there data starts from that I.P. then from next "testing" cell for next IP.
 

Attachments

  • Metro_MTU_Check LM.xlsb
    44.9 KB · Views: 0
And thanks Narayan Sir, I understand there is some manipulation in data; some time "testing" cells begins after 19th or 21st column. That's why i requested if it is possible to break data from every cell which contains "testing pss node".
 
Hi Premjeet ,

See this file. HeaderRange is a named range.

Narayan
 

Attachments

  • Copy of Metro_MTU_Check LM.xlsb
    74.7 KB · Views: 0
Sorry for bothering you Narayan Sir, Seems i am unable to explain you my clear view also it seems my desired result not possible by formula. Because your shared result is able to present only two result of table requirement. I am just sharing original dump with you. Hope it is able give you a clear view.

Thanks again..
 

Attachments

  • Metro_MTU_Check.xlsb
    29.7 KB · Views: 2
Wow, I was really in suspicion that it is not possible without VBA but you did it.
You rock.. Awesome Sir..:awesome:

Small help how does this formula work. A little elaboration please.

Thank you very much..:)
 
Hi Premjeet ,

Actually there is no one formula because the data layout is not constant ; there are quite a few places where the formula has been changed.

However , the general idea is as follows :

1. The first column of output ( column B ) is looking at the text Testing PSS node , and where ever it finds this text , it puts it in that cell.

2. All the other columns are then populated with data which is to be found after that column ; thus , suppose the second instance of the text Testing PSS node is found in column U , then this is put in cell B2 ; thereafter , cell C2 gets the entry in V2 , D2 gets the entry in W2 , and so on.

Similarly , the third instance of the text Testing PSS node is found in column AN , then this is put in cell B3 ; thereafter , cell C3 gets the entry in AO2 , D3 gets the entry in AP2 , and so on.

The problem with this is that the data does not match in some places ; thus the column for MTU size may contain some other kind of data ; in this case , I have modified the formula to take care of this.

Narayan
 
You must log in or register to reply here.
Back
Top