functions and formulas

New Contributor

good morning, i have a data sheet, one column has the letters in there, however, there is a comma after each letter.  the report that i received from the other team has all of the info, however, they have it on one line each.  how or what function can i use to pull the info from the team that has them on one line, and make sure i capture all of the info onto my listing.  i.e. my list has control number and then the next column has the letters, a,b,c.  their report has the control number on one line, and then the next column has the letter a, next line on their report, same control number, but letter b.  next i would need to know if all of the info that they received is on my listing so they have one more column that i would need to add to my report yes/no.  

if someone knows how to do this, i would really appreciate any help.  thank you.

7 Replies

@Janet1890 It sounds like something that can be done with Power Query. Can you upload some example data with both listings (yours and the other team's).

@Riny_van_Eekelen thank you for getting back to me.  i have uploaded what the other team sent to me and what i have.  so trying to figure out how to do it without doing this manually. lol   thank you.

@Janet1890 Right now I'm unable to respond. Perhaps I'll be back tomorrow morning (my time) CET.

@Riny_van_Eekelen  - GM - i never received a response on how to do this.  pls advise and thank you.

@Janet1890 Sorry! It slipped my mind. There's no way to mark a post as unread or for follow-up.

I've pulled the information from the Other Team in  the My Team file. It's easier to demonstrate that way.

 

Used PowerQuery  to reshape the "My Team" data so that it will match the structure of the Other Team list. I found no matching control numbers between the two, so you would want to append the two to get a complete list. Is that what you had in mind?

@Riny_van_Eekelen - that would be the only way that i can do that.  do you think you might be able to write out step by step instruction for me? i.e. 1) pull rpt 2) separate your rpt to single columns doing this,  3) do power query doing this...something like that.  it would help me.  thanks,

@Janet1890 If you are not familiar with PQ at all, the link below could be a good starting point. With that knowledge you should be able to follow the query steps in the file I uploaded.

https://exceloffthegrid.com/power-query-introduction/ 

 

It's a bit of a learning curve, but once you get the hang of it, you'll love it.