Moving data from columns to rows based on cell value

Hello all,

I've just registered as I'm really stumped with this issue. I have a set of data which lists every interaction we've had with our clients, with a separate row for each type of interaction (meeting, email etc.). I've been asked to extract the following data:

• clients we have met in person
• clients we have (only) had email interaction with e.g. haven't met in person
• clients we have on record that we haven't met or emailed

The last item in the list is where we may have been made aware of a client and made some notes/had a meeting about them, but haven't interacted with them directly.

I'm really struggling to go from the raw data to getting the data from the list above.

I thought perhaps if I could get all the 'interaction types' into the same row then I could use some sort of filter or IF function to separate it out? But if anyone has any better ideas then I'd be very grateful! The problem I'm having with doing that would be getting the 'interaction type' data to match the client it's related to, if that makes sense?

I've attached some example data and a (potential) desired outcome on a separate sheet within the Excel book.

Thanks in advance!

I'd suggest you format your source data as a table, then use Power Query (Get & Transform Data) to reshape it a bit:

Please Login or Register  to view this content.

Now you can filter clients based on the number of interactions, by type...

Moving forward, you could add logic to define contact categories, based on counts by type (e.g. No physical contact = clients where Meetings = 0 and Staff Meetings = 0, for example)