Cani use the offset formula for the following problem?

**ahsng** · 06-26-2013, 05:35 AM

Hi everyone, in a bit of a pickle...I get two pivot tables showing agent names and logins in one and agent names and city searches in the other, I want to combine them to make one table showing agent logins and cities searched, the problem I have is that the logins table has a single row per agent and the cities table can have more than one row per agent depending on the number of cities searched e.g.

Agent Logins
Pink 5
Blue 1

Agent Cities
Pink Cairo
Barcelona
Las Vegas
Norway

Blue New York city

Is there anyway to do a v-look-up that returns the cities for each agent, and recognizes the next agent name and stops there? If it's any help the city names are indented under each agent name?

Is there a VBA solution for this?

Appreciate the help, thanks!

**Richard Buttrey** · 06-26-2013, 05:38 AM

Hi,

You'll need to upload your workbook so that we can see your request in context. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**ahsng** · 06-26-2013, 05:58 AM

This is what I get before:

**ahsng** · 06-26-2013, 06:02 AM

This is what i'm looking for after:

**ahsng** · 06-26-2013, 06:03 AM

Hi Richard, thanks for your response, I hope the above attachments are of some use to you...

**Richard Buttrey** · 06-26-2013, 08:20 AM

Please upload a workbook or a representative cut down copy as requested, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well. The solution to your problem may come directly from the data on which your pivot tables are based and not on the tables themselves.

Please don't upload pictures. None of us are inclined to recreate your data when you have a workbook available - see guidelines in the Rules area of this forum.