I got a datasheet that looks like this: And based on this data I want to make a sheet that looks like this: So what did I do to get to the second picture? In the data from the first picture I searched for all the Numberswith6+digits that have the same first 6 numbers. That gives us 2 results, 445566 that exists 3 times and 112233 that exists 2 times. From there I want to find the highest percentage for each result, so what is the highest percantage in %store for 445566, that's 10%. After doing this for every store you get one line for each 6 number result with all the highest % per store. As you can imagine, doing this by hand is quite time consuming. I was wondering if anyone knows how I could speed this up. I thought of trying to use Vlookup/VBA, but I don't quite understand enough of these subjects to solve this problem (basic knowledge of Vlookup and almost non-existant knowledge of VBA). I was wondering if any of you can send me in the right direction or have any ideas that can make this process easier. Thanks in advance EDIT: link of data picture 1: https://drive.google.com/open?id=0By2vDlsDCzUoZ1BocV9qVjQ4bzg

excelexcel-formulavba

CvP

Reputation: 364

Returning the highest values from multiple selections

I got a datasheet that looks like this:

And based on this data I want to make a sheet that looks like this:

So what did I do to get to the second picture?

In the data from the first picture I searched for all the Numberswith6+digits that have the same first 6 numbers. That gives us 2 results, 445566 that exists 3 times and 112233 that exists 2 times.
From there I want to find the highest percentage for each result, so what is the highest percantage in %store for 445566, that's 10%.
After doing this for every store you get one line for each 6 number result with all the highest % per store.

As you can imagine, doing this by hand is quite time consuming. I was wondering if anyone knows how I could speed this up. I thought of trying to use Vlookup/VBA, but I don't quite understand enough of these subjects to solve this problem (basic knowledge of Vlookup and almost non-existant knowledge of VBA). I was wondering if any of you can send me in the right direction or have any ideas that can make this process easier.

Thanks in advance

EDIT: link of data picture 1: https://drive.google.com/open?id=0By2vDlsDCzUoZ1BocV9qVjQ4bzg

Upvotes: 0

Answers (3)

Marco Vos

Reputation: 2967

Put the following formula in cel H2. Use Ctrl+Shift+Enter and copy down.

=IFERROR(INDEX(LEFT($A$2:$A$100,6), MATCH(0,COUNTIF($H$1:H1, LEFT($A$2:$A$100,6)), 0)),"")

This will give you the unique 6 digit numbers.

Then put the next formula in cell I2 use Ctrl+Shift+Enter again. Copy down and to the right.

=MAX(IF(LEFT($A$2:$A$100,6)=$H2,B$2:B$100))

The second formula will provide the max percentages per unique number, per store.

Upvotes: 2

user4039065

Reputation:

In H2:J2 per the supplied image,

'as an array formula with CSE in H2
=--LEFT(INDEX(A$2:INDEX(A:A, MATCH(1E+99, A:A)), MATCH(0, COUNTIF(H$1:H1, LEFT(A$2:INDEX(A:A, MATCH(1E+99, A:A)), 6)&""), 0)), 6)
'as a standard formula in I2
=MAX(INDEX((B$2:INDEX(F:F, MATCH(1E+99, F:F)))-(--LEFT(A$2:INDEX(A:A, MATCH(1E+99, F:F)), 6)<>H2)*1E+99, , ))
'as a standard formula in J2
=INDEX(A$1:F$1, AGGREGATE(15, 6, COLUMN(A:F)/((--LEFT(A$2:INDEX(A:A, MATCH(1E+99, F:F)), 6)=H2)*COUNTIF(I2, A$2:INDEX(F:F, MATCH(1E+99, F:F)))), 1))

Fill down as necessary. Add IFERROR wrappers to avoid error codes when you run out of things to match.

Upvotes: 1

yass

Reputation: 869

You can use:

=MAX(IF(ISNUMBER(FIND($A8,$A$2:$A$6)),B$2:B$6,0%))

Array Formula press Ctrl+Shift+Enter instead of just Enter
and drag it down and in the same row
First Create the Column of 6 digits starting in A8 for example
A2:A8 the column of Numbers
B2:B6 % store
change it to correspond the Data in the first Table
Keep the $ for fixed references so it change the right way

Upvotes: 2

Returning the highest values from multiple selections

Answers (3)

Related Questions