cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
The Discovery Summit 2025 Call for Content is open! Submit an abstract today to present at our premier analytics conference.
Choose Language Hide Translation Bar
View Original Published Thread

Stack side by side tables from excel and add header columns

LK2
LK2
Level I

I have been struggling for 2 days pulling data from excel into JMP in the correct format.

 

I have hundreds of excel sheets, but the data is disorganised. There are many tables next to eachother (hundreds) in an excel sheet.

I want to stack the data across the different tables, but also make new columns with the associated header data. I have only been able to achieve this on one table, but can't replicate it across all tables, or sheets across different excel docs. The sheets also tend to have different tab names, but if i can just get it to work on one excel document that would be very good.

 

LK2_1-1741955361331.png

 

 

3 REPLIES 3
jthi
Super User


Re: Stack side by side tables from excel and add header columns

Can you provide an example excel file? And if you have few different types of files, having more examples will make it easier to provide answers.

-Jarmo
LK2
LK2
Level I


Re: Stack side by side tables from excel and add header columns

Hi Jarmo,

 

Absolutely, i've attached some dummy data. There can be up to maybe 200 tables side-by-side, and i have added different worksheets within the same excel file just to show the tab names change, but actually these sheets are often in separate excel sheets within a folder. The yellow highlights are where new headers would be needed i believe, and the orange highlight is just there because in the actual data set there are a few hundred empty rows before this data starts below it.

 

Ideally i'd like to bring in all of the parameter (parameters 1-19, there are more when you scroll down in the excel) under a parameters header, and the values under a value header, and add columns to show that this data belongs to header1/2/3 (and new 4 ) values by adding columns as in the original post.

 

Ultimately the two sets of data (VCL and VL header columns) i will also be looking to save similarly with some identifier from header1/2/3 (and 4).

 

Let me know if you have any thought, it would be much appreciated!

Thanks

Lewis

jthi
Super User


Re: Stack side by side tables from excel and add header columns

It won't be easy, as to be honest, this is horribly formatted data. Going to the source and fixing the data there would be the best option but it is rarely an option.

 

I would start by pulling the data into JMP as raw as possible, so something like this

jthi_2-1741973453990.png

jthi_3-1741973463892.png

And from this I would start building a script as this data has too many issues to deal without using JSL (you can most likely get quite far without JSL, but it with JSL it will be much easier). Fixing the first line of headers is most likely the first thing you should start with as then you can start more easily separating the "tables" and their data

 

-Jarmo