Excel Help Please!!
Author
Discussion

The Moose

Original Poster:

23,710 posts

240 months

Friday 31st August 2018
quotequote all
I don't know if it's just because it's a Friday afternoon before the long weekend, or what, however I'm struggling to do something in Excel - please help! This is to assist someone else - no good deed and all that!

I have 3 sets of data in Excel (different sheets in same workbook).

The sets of data contain the same columns, just different data (i.e. cover different periods of time). Columns are:
Store Locations, No Visitors, No Purchases, Turnover

There are between 500 and 1,000 lines in each set of data.

I first need to look at each list and highlight the lines that are in each of the other 2 lists (some entries are in all 3 lists, some in 2 and some just in one).

The second thing I need to do is to bring this all into one sheet with duplicate columns. I.e Store Locations down the left and then No Visitors 1, No Purchases 1, Turnover 2, No Visitors 2, No Purchases 2, Turnover 2, No Visitors 3, No Purchases 3, Turnover 3.

Is there an easy way to do this that I am just missing?!

Doofus

34,707 posts

204 months

Friday 31st August 2018
quotequote all
Presumably the thing you want to match is the store location.

In which case, make a list of all possible locations in a new tab, set up your columns and use VLOOKUP to pull data into each cell from the first three tabs.

If one location doesn't exist in one tab, then VLOOKUP will return #NA, so preface your VLOOKUP with IF(ISNA.



Written on the assumption that you do know Excel, but are just suffering from brain fade smile

The Moose

Original Poster:

23,710 posts

240 months

Friday 31st August 2018
quotequote all
Of course!

I can just chuck all the store locations in a list and deduplicate...I don't know/have a list of all the locations these are all just subsets of

Henners

12,424 posts

225 months

Friday 31st August 2018
quotequote all
The Moose said:
I don't know/have a list of all the locations these are all just subsets of
Eh? I don't get this bit.

Surely you have all the locations, in the lists?...

The Moose

Original Poster:

23,710 posts

240 months

Friday 31st August 2018
quotequote all
Henners said:
The Moose said:
I don't know/have a list of all the locations these are all just subsets of
Eh? I don't get this bit.

Surely you have all the locations, in the lists?...
I'm sorted now, but thank you!

What I meant was there are other locations that are not in the lists.

Henners

12,424 posts

225 months

Friday 31st August 2018
quotequote all
beer