Consolidating several smart sheets in one (appending columns)

Fernando Plancic

Hi. I have several smart sheets with 2 columns, being the first column common to all. What is required is to have an aggregated smartsheet with the common first column and append the second columns of the other smart sheets.

I.E

Smarthsheet 1

Subject    Juan

math           A

physics       B

Smartsheet 2 

Subject    Peter

math           A-

physics       C

 

Aggregated Smartsheet 

Subject    Juan  Peter

math           A       A-

physics       B       C

 

I have tried using reports but it added as new rows.

Thanks.

Andrée Starå

Hi Fernando,

Depending on how many records you have you could use Cell-linking. Another option would be to use cross-sheet formulas.

How many rows/cells are there in one sheet? Are the records on the same rows in the different sheets? (cell-linking won't work otherwise)

The sheets would be linked/collected on another sheet.

Would that work for you?

I hope this helps you!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

You could use an INDEX/MATCH with x-sheet references. I would need some screenshots and/or more details to be able to give you something more specific though.

Fernando Plancic

Hello Paul. Each Sheet have 4 columns. The first 3 are common, so, should be populated only once in the aggregated sheet. The number of rows of each sheet is about 1700 lines.

The forth column of each sheet, which is the one required to be populated in the aggregated sheet, will contain a user in the header and alphanumeric values in the rest of the cells.

Is pretty much what I have posted in the first question, but with a larger number of rows (about 1700).

Thanks!

Fernando.

 

Fernando Plancic

More information. The total number of sheets is about 180. So, the aggregated sheet should have 3+180 columns with about 1700 rows.

Fernando Plancic

Hello Andrée

Each Sheet have 4 columns. The first 3 are common, so, should be populated only once in the aggregated sheet. The number of rows of each sheet is about 1700 lines.

The forth column of each sheet, which is the one required to be populated in the aggregated sheet, will contain a user in the header and alphanumeric values in the rest of the cells.

Is pretty much what I have posted in the first question, but with a larger number of rows (about 1700).

The total number of sheets is around 180, so the final aggregated sheet would have 3 (common) + 180 rows, and 1700 lines.

Thanks!

Fernando.

Paul Newcome

Is each sheet an individual name with various subjects? What kind of data do you have where?

J. Craig Williams

It sounds like you are trying to transpose the data. The Smartsheet is limited to 200 columns.

It seems you would be better off having the subjects (math, physics) as columns and the students (Juan, Peter) as rows.

Why do you have 180 sheets?

This seems like it requires a redesign.

What am I missing?

Craig

Andrée Starå

Happy to help!

I agree with Craig's comment. How is your set up structured?

Paul Newcome

I've been thinking the same thing about having the subjects as columns and the students as rows, but apparently each sheet already has 1700 rows in it.

 

That's why I'm trying to figure out exactly what kind of data is being populated where and why.

 

Screenshots would be SUPER helpful.

Fernando Plancic

Hi all. Each sheet will belong to a resource of my team. What I'm trying to do is to have their auto evaluation in this 1700 categories. I know it sounds too much but there are 1700 categories (subjects) defined in the company and I should attain to it.

So the idea is to create one smartsheet per user and make each user editor of his sheet. With this they will be able not to only fill but to update once they gather a new knowledge or certification.

Finally the idea is to have all this info in only one sheet, where I can look for a category (subject) and know who in the team has the required level of kwnowledge.

I have transpose each sheet and run a report. Seems to be close, but the problem now is that I have the subjects duplicated. Even so, having 180 columns at max will not work in my case.

 

Report

Subject    math   physics

Juan            A      B

Subject    math   physics

Peter        A-       C

 

I should be able to have the subject row only once.

 

Thanks.

Fernando.

Paul Newcome

The only thing I can think of at this point would be to put each person across the columns starting at column2. In row one use a "JOIN(COLLECT(IF(ISTEXT" kind of thing to make one long string of each certification per person. In Column 1 starting in row 2, put your list of 1700 certifications. You can then use checkbox columns with an IF(FIND(Certification@row, [Current Column Name]$1) > 0, 1) to search the collective list of certificates for the person in that column and check the box if the certification in that row found in that person's list at the top of the column. Transposing of the actual grades would have to be manually done due to the x-sheet reference limitations.