Transposing Complex Data into Usable Format

Hi All,

I know there are plenty of super intelligent excel wizards on here so im hoping for some help.

I have a file which has hundreds of site IP & Subnet information and Site ID's that isnt presented in the most helpful way. Im looking to take the information which is in multiple rows/columns and need them transposing into multiple columns but the information is not easy to manipulate (IMHO). I have looked at "transpose" but its not working as i need and im at a loss.

Attached is a spreadsheet with "Current" how it is now" and "How Id Like It To Be".

Hoping someone can help get the information to where i need it to be.

Thanks all
Craig

Hi Craigc,

We'd love to help but that darn spreadsheet isn't attached. Sometimes it is too big and you need to trim it down a bit to have it fit. We're waiting for a fun problem. My guess is Pivot Tables but need to see what you have.

OK craig,

Your data isn't good for doing Excel work with it. I've switched the data around a bit and used a newer XLookup() function on it. I'm not too proud of my answer, but it works. You will need to put the 1s and 2s above the table and use that helper column also. Keep asking and one of the smart Guru's will give ya a better answer.
XLookup using 2 columns.xlsx

Hi Craig,

Power Query might be able to do the job. Do you get a sheet daily? Are there more than 5 columns in your data? If so then I'll try a PQ answer.

Ok - did a Power Query Answer in the attached after reading:
https://www.myonlinetraininghub.com/...0d17e333b1981b
I used method 2.
Power Query Merge using Offset Indexs.xlsx

Col G (old) array formula:

Please Login or Register  to view this content.

H5:J7

Please Login or Register  to view this content.

Administrative Note:

Members will tailor the solutions they offer to the version (NOT release number) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. As you are still new here, I have done it for you this time.)

Hi Craig,

I was worried that you would have 30 and 40 so I still think an extension of the Power Query is what you really want. I hope Pete can work an answer for you, but it might be time to learn a little PQ.

Hey MarvinP,

Thanks for your reply - I did start to attempt looking at this but it got quite late and I have Excel for Mac so found it different windows etc so it's a little more difficult to work out what to do - don't suppose you have any other guides at all?

As you can tell - fairly inexperienced wity excel.

Thanks
Craig

Administrative Note:

For the second time of asking, Craig ...

Members will tailor the solutions they offer to the version (NOT release number) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

Are there ALWAYS 4 vlans per site... or sometimes 2... sometimes 3...

No reply... so this is for the Q as asked:

to return site ID:

=FILTER(C5:C24,A5:A24="Site ID")

to return the vlan nos.

=INDEX(INDEX($C$5:$D$22,MATCH($G12,$C$5:$C$22,0)+2,):INDEX($C$5:$D$22,MATCH($G12,$C$5:$C$22,0)+4,),INT(SEQUENCE(,4,,1/2)),1+MOD(SEQUENCE(,4,0),2))

see first file. If there are ALL 4 vlans present you can the same first formula (with a longer range, as needed), and:

=INDEX(INDEX($C$5:$D$26,MATCH($G14,$C$5:$C$26,0)+2,):INDEX($C$5:$D$26,MATCH($G14,$C$5:$C$26,0)+6,),INT(SEQUENCE(,8,,1/2)),1+MOD(SEQUENCE(,8,0),2))

see second file.

If it is sometimes 2, 3, or 4... then provide a REALISTIC sample file.

Not quite there yet! Which version is it? Please see my instructions about how to find out. I am looking for something like MS365 in your profile.