Stuck trying to get common value, please help

M

mmallonee

New Member
Joined
Nov 4, 2013
Messages
2
Here is what I am trying to accomplish, I have two columns column "A" has many different numbers and column "B" has different prices associated to column "A". I am tring to find the most common price in column B as it relates to A without using a pivot. EXAMPLE 1 & 2 of result desired below. As you can see column A (item) will have several duplicate values and columb B (price) may or may not but I need an easy way of locating most common for each item. I have close to 2000 items and 180K lines of results so you can see how a pivot would be a manual process. I would not be apposed to removing duplicates of items in another column like in example 2 to get the results. It's like I need a MODE IF statement or something but I can not figure anything out, I am using excell 2007 and any help would be apprechiated. Thanks

EXAMPLE 1
[TABLE="width: 206"]
<tbody>[TR]
[TD="width: 99, bgcolor: transparent"]ITEM
[/TD]
[TD="width: 64, bgcolor: transparent"]PRICE
[/TD]
[TD="width: 112, bgcolor: transparent"]MOST COMMON
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]398211
[/TD]
[TD="class: xl67, bgcolor: transparent"]35.99
[/TD]
[TD="class: xl67, bgcolor: transparent"]39.99
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]398211
[/TD]
[TD="class: xl67, bgcolor: transparent"]35.99
[/TD]
[TD="class: xl67, bgcolor: transparent"]39.99
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]398211
[/TD]
[TD="class: xl67, bgcolor: transparent"]39.99
[/TD]
[TD="class: xl67, bgcolor: transparent"]39.99
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]398211
[/TD]
[TD="class: xl67, bgcolor: transparent"]39.99
[/TD]
[TD="class: xl67, bgcolor: transparent"]39.99
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]398211
[/TD]
[TD="class: xl67, bgcolor: transparent"]39.99
[/TD]
[TD="class: xl67, bgcolor: transparent"]39.99
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]398211
[/TD]
[TD="class: xl67, bgcolor: transparent"]39.99
[/TD]
[TD="class: xl67, bgcolor: transparent"]39.99
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]398211
[/TD]
[TD="class: xl67, bgcolor: transparent"]39.99
[/TD]
[TD="class: xl67, bgcolor: transparent"]39.99
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]398216
[/TD]
[TD="class: xl67, bgcolor: transparent"]25.99
[/TD]
[TD="class: xl67, bgcolor: transparent"]48.99
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]398216
[/TD]
[TD="class: xl67, bgcolor: transparent"]48.99
[/TD]
[TD="class: xl67, bgcolor: transparent"]48.99
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]398216
[/TD]
[TD="class: xl67, bgcolor: transparent"]48.99
[/TD]
[TD="class: xl67, bgcolor: transparent"]48.99
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]398216
[/TD]
[TD="class: xl67, bgcolor: transparent"]48.99
[/TD]
[TD="class: xl67, bgcolor: transparent"]48.99
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]398216
[/TD]
[TD="class: xl67, bgcolor: transparent"]48.99
[/TD]
[TD="class: xl67, bgcolor: transparent"]48.99
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]398216
[/TD]
[TD="class: xl67, bgcolor: transparent"]48.99
[/TD]
[TD="class: xl67, bgcolor: transparent"]48.99
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]398216
[/TD]
[TD="class: xl67, bgcolor: transparent"]48.99
[/TD]
[TD="class: xl67, bgcolor: transparent"]48.99
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]398216
[/TD]
[TD="class: xl67, bgcolor: transparent"]48.99
[/TD]
[TD="class: xl67, bgcolor: transparent"]48.99
[/TD]
[/TR]
</tbody>[/TABLE]

EXAMPLE 2


[TABLE="width: 268"]
<colgroup><col style="width: 74pt; mso-width-source: userset; mso-width-alt: 3620;" width="99"> <col style="width: 48pt;" width="64"> <col style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;" width="61"> <col style="width: 50pt; mso-width-source: userset; mso-width-alt: 2413;" span="2" width="66"> <tbody>[TR]
[TD="width: 99, bgcolor: transparent"]ITEM [/TD]
[TD="width: 64, bgcolor: transparent"]PRICE [/TD]
[TD="width: 61, bgcolor: transparent"][/TD]
[TD="width: 66, bgcolor: transparent"]ITEM [/TD]
[TD="width: 66, bgcolor: transparent"]PRICE [/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]398211[/TD]
[TD="class: xl69, bgcolor: transparent"]35.99[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"]398211[/TD]
[TD="class: xl69, bgcolor: transparent"]39.99[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]398211[/TD]
[TD="class: xl69, bgcolor: transparent"]35.99[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"]398216[/TD]
[TD="class: xl69, bgcolor: transparent"]48.99[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]398211[/TD]
[TD="class: xl69, bgcolor: transparent"]39.99[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]398211[/TD]
[TD="class: xl69, bgcolor: transparent"]39.99[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]398211[/TD]
[TD="class: xl69, bgcolor: transparent"]39.99[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]398211[/TD]
[TD="class: xl69, bgcolor: transparent"]39.99[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]398211[/TD]
[TD="class: xl69, bgcolor: transparent"]39.99[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]398216[/TD]
[TD="class: xl69, bgcolor: transparent"]25.99[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]398216[/TD]
[TD="class: xl69, bgcolor: transparent"]48.99[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]398216[/TD]
[TD="class: xl69, bgcolor: transparent"]48.99[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]398216[/TD]
[TD="class: xl69, bgcolor: transparent"]48.99[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]398216[/TD]
[TD="class: xl69, bgcolor: transparent"]48.99[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]398216[/TD]
[TD="class: xl69, bgcolor: transparent"]48.99[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]398216[/TD]
[TD="class: xl69, bgcolor: transparent"]48.99[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]398216[/TD]
[TD="class: xl69, bgcolor: transparent"]48.99[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
Hi,

Assuming the data you give above is in A1:B16 (with headers in row 1), this array formula in C2 and copied down:

=MODE(IF($A$2:$A$16=A2,$B$2:$B$16))

Regards
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Help with FilterXML formula
Replies
0
Views
2K
montex01
M
E
  • Question Question
Can anyone help me get my VBA code working? I have spent tons of time but can't quite fix it.
Replies
3
Views
4K
profray
P
M
  • Solved
Combine non-blank items in two ranges (unknown number of values) into a new list
Replies
9
Views
2K
Eric W
Eric W
T
  • Question Question
How can I make queryselector to properly loop through a range of cells and get results from a webpage
Replies
1
Views
2K
Worf
Worf
A
  • Question Question
Identifying Items with the most in common
Replies
2
Views
506
ahall351
A

Forum statistics

Threads
1,226,737
Messages
6,192,736
Members
453,752
Latest member
Austin2222

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top