Append to one row at a time to cluster of rows

[itahir04]

Hi team,

How do I append School, Admission Coloumn first with Joe, Then with Dan, soforth.

I am doing this by selecitng one row of student (i.e Joe, PA), then selecting two coloumns (School, Admission Status 1) and using append function.

But I have large data set, If I have 50 Students, I would have to select each row of student.

Can you please advise me an easy logic.

Sample data set:

Student Name	State
Joe	PA
Dan	UT
Lee	CA
School	Admission Staus 1	Admission Staus 2	Admission Staus 3
Harvard	Accept Status 1	Reject Status 2
MIT	Reject Status 1	Accept Status 2
NJIT	Accept Status 1	Reject Status 2

Required out put:

School	Admission Sttaus	Student Name	State
Harvard	Accept Status 1	Joe	PA
MIT	Reject Status 1	Joe	PA
NJIT	Accept Status 1	Joe	PA
Harvard	Reject Status 2	Dan	UT
MIT	Accept Status 2	Dan	UT
NJIT	Reject Status 2	Dan	UT
Harvard		Lee	CA
MIT		Lee	CA
NJIT		Lee	CA

Thanks,

[Aaron_Harter]

@itahir04 if you separate the data into two tables, one containing the school information and the other the student data, you can accomplish this with a Transpose and Append Fields tool:

[itahir04]

Hi Harter,

That's the challenge.

In your data, you are appending each school with three students.

Status 1 is only for Joe

Status 2 is only for Dan

and blank is for Lee.

I can transpose and append, but I get results like yours,

My Out put should be:

School	Admission Sttaus	Student Name	State
Harvard	Accept Status 1	Joe	PA
MIT	Reject Status 1	Joe	PA
NJIT	Accept Status 1	Joe	PA
Harvard	Reject Status 2	Dan	UT
MIT	Accept Status 2	Dan	UT
NJIT	Reject Status 2	Dan	UT
Harvard		Lee	CA
MIT		Lee	CA
NJIT		Lee	CA

or if I sort same table on school Asc:

School	Admission Sttaus	Student Name	State
Harvard	Accept Status 1	Joe	PA
Harvard	Reject Status 2	Dan	UT
Harvard		Lee	CA
MIT	Reject Status 1	Joe	PA
MIT	Accept Status 2	Dan	UT
MIT		Lee	CA
NJIT	Accept Status 1	Joe	PA
NJIT	Reject Status 2	Dan	UT
NJIT		Lee	CA

I am not sure how to achieve that.

Thanks,

Rana

[Aaron_Harter · Accepted solution]

@itahir04 Rana, thanks for specifying that the status # corresponds to the position of the student in the student table.  You can assign a record ID, then use a formula to create a string that will match to the other table in the Join:

[itahir04]

Thank you so much, Very very helpful.

Regards,

Rana