Formulas: Sorting pivot data based on simple logic

Andrew4119

New Member
Joined
Jan 7, 2018
Messages
1
I'm currently trying to sort 2 variables nested in a pivot based on certain arguments.
TL;DR for some context:
(Image best explains) https://i.stack.imgur.com/HKAV3.png
L is pivot; N is desired output

  1. Sheet with Codes and Names attached to them - All codes have Len=4
  2. Codes can be unique and have only one name attached to them. In that case I want to sort the Code-Name as being "Single"
  3. Names do repeat themselves and are not individual to codes
  4. A code with different multiple Names needs to be sorted as "Multiple"
  5. A code with identical multiple Names needs to be sorted as "Single"
  6. I have used a & and Countif to address duplicate names with a uniqued identifier
At the moment I'm quite stuck and unsure how to proceed. Based on the logic i tried<code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; font-size: 13px; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; background-color: rgb(239, 240, 241); white-space: pre-wrap;">=IF(LEN(A4)=4,A4,IF(AND(OR(LEN(A3)=4,OR(LEFT(A4,10)=LEFT(A3,10),LEFT(A4,10)=LEFT(A5,10))),OR((LEN(A5)=4),A5="")),"Single","Multiple"))</code> output fails to return Single/Multiple when bullet 5.
Hope I gave enough insight. Any idea would help.
Thank you
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Watch MrExcel Video

Forum statistics

Threads
1,123,268
Messages
5,600,624
Members
414,394
Latest member
mahendar

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
Top