[ms office - 2019] Excel Formatting based on values

[caffrey]

I'm not even sure how to word this, and I'm having issues concentrating today but

 

I have a row like

 

headers

Number, col1,col2,col3,col4,col5

 

150,1,1,1,0,0

 

And I would like a sheet to take that data and do this :-

 

Number,col1

 

150,1

150,1

150,1

150,0

150,0

 

Eg split the columns into separate rows if that makes sense

 

Can't for the life on me work it out today!

[mavhc]

Data> Text to columns, delimited, comma

[Seb1780]

HLOOKUP

VLOOKUP

 

Depends where your "Number" data comes from and if there are the same number of results (columns) for each

Edited September 30, 2020 by Seb1780

[caffrey]

Not quite what I'm looking for I think. I'd upload an image but image upload is broken

 

Roll No	Business	Psychology	Drama	Biology	Sport Studies	French	German	English Language	English Literature	EPQ	Economics	Mathematics	Physics	Chemistry	Latin	Geography	History
														
1111				                 A*		         A							                 	                                A			

 

And I'd like a sheet of results to show

 

1111	Biology	A*
1111	French	A
1111	Chemistry	A

[Seb1780]

I use INDEX/MATCH when converting results broadsheets into columnar listings for use with third party systems - but this does rely on the first two columns being populated then looking up the results.

[caffrey]

Might just change the source database table to match

[skinsley]

I find ASAP utilities (free version) very useful for things like this.

[caffrey]

Thanks, I ended up doing it in really hacky way but it worked (Embarrassed to even post how I did it )

[nadeem]

I find ASAP utilities (free version) very useful for things like this.

 

 

This is definitely worth downloading and having a look for the amazing amount of things it can do - been using it for years and years now