Excel Extracting list of data from column when criteria selected

Hi Experts

Hope you can help

I am putting together a report for my month end meeting and it covers three sets of costs I have negotiated with three potential candidates to handle our freight

The data is OK but on the third sheet Id like to put together a simple SWOT analysis sheet based on the three candidates

What Id like to do is to have a drop down list (in D2) to select the desired Candidate and ‘pull’ the relevant data into each corresponding square. For example, in column V, I have Strengths against each candidate. When I select candidate 1, for example, Id like to extract the information from cells V4 to V10, into the Strengths section of the SWOT Model and display this in D5 to D16?

Ive tried methods of indexing and merging to try and get the block of data for each section to appear when the candidate is selected but to no avail.

I have attached the sheet (the sheet in question is titled SWOT) and I have placed the text in RED what Id like to see in the sections when the corresponding candidate is selected (ive used Candidate 1 for this purpose)

Any suggestions would be very welcome

J
Forwarder-Benchmark.xlsx
Jase AlexanderCompliance ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ShumsDistinguished Expert - 2017Commented:
Hi Jase,

This can easily be achieved through Pivot Table.

I am not sure about FCL & LCL sheets. I copied data from SWOT sheets to new workbook, filled Candidates, deleted blank rows, converted data to table then created a pivot table following below steps:
  1. Select Data and navigate to Insert/Pivot Table
  2. Drag Candidates in Report Filter
  3. Drag S, W, O, T in Row Labels
  4. Navigate to Design/Subtotals - Select Do Not Show SubTotal
  5. Navigate to Design/Grand Totals - Select Off for Rows And Columns
  6. Navigate to Design/Report Layout - Select Show in Tabular Form
  7. That's it
Change any Candidate from B1 and get the required result
Filtered PivotCheck in attached..
Jase_Alexandar_SWOT.xlsx
0
Rob HensonFinance AnalystCommented:
See attached with the second version of INDEX function.

Second Version of Index syntax is:
=INDEX(Reference,RowNum,ColNum,AreaNum)

This converts to a formula of:
=IFERROR(INDEX(($V$4:$Y$10,$V$12:$Y$18,$V$20:$Y$26),$C5,1,MATCH($D$2,$U$29:$U$31,0)),"")

Reference - The three sets of ranges for your three data sets
RowNum - I have added a row reference in column C
ColNum - This refers to the column of data in your table of entries; 1 - Strengths, 2 - Weakness, 3 - Opportunities, 4 - Threats
AreaNum - This looks at the Candidate name in D2 and mataches it in the list of Candidates to determine which area to use.

Hope this helps
Forwarder-Benchmark.xlsx
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jase AlexanderCompliance ManagerAuthor Commented:
Hi Guys

Thank you so much for the quick response

Both options have given me oppotunities to approach this task and I am extremely grateful for your detailed explanations too.

J
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Rob HensonFinance AnalystCommented:
Amended to make more dynamic and to get rid of zeros when no entry on a particular row:

Strengths
=IF(OR(ISERROR(INDEX(($V$4:$Y$10,$V$12:$Y$18,$V$20:$Y$26),$C5,MATCH(D$4,$V$3:$Y$3,0),MATCH($D$2,$U$29:$U$31,0))),ISBLANK(INDEX(($V$4:$Y$10,$V$12:$Y$18,$V$20:$Y$26),$C5,MATCH(D$4,$V$3:$Y$3,0),MATCH($D$2,$U$29:$U$31,0)))),"",INDEX(($V$4:$Y$10,$V$12:$Y$18,$V$20:$Y$26),$C5,MATCH(D$4,$V$3:$Y$3,0),MATCH($D$2,$U$29:$U$31,0)))

Weaknesses
=IF(OR(ISERROR(INDEX(($V$4:$Y$10,$V$12:$Y$18,$V$20:$Y$26),$C5,MATCH(K$4,$V$3:$Y$3,0),MATCH($D$2,$U$29:$U$31,0))),ISBLANK(INDEX(($V$4:$Y$10,$V$12:$Y$18,$V$20:$Y$26),$C5,MATCH(K$4,$V$3:$Y$3,0),MATCH($D$2,$U$29:$U$31,0)))),"",INDEX(($V$4:$Y$10,$V$12:$Y$18,$V$20:$Y$26),$C5,MATCH(K$4,$V$3:$Y$3,0),MATCH($D$2,$U$29:$U$31,0)))

Opportunities
=IF(OR(ISERROR(INDEX(($V$4:$Y$10,$V$12:$Y$18,$V$20:$Y$26),$C19,MATCH(D$18,$V$3:$Y$3,0),MATCH($D$2,$U$29:$U$31,0))),ISBLANK(INDEX(($V$4:$Y$10,$V$12:$Y$18,$V$20:$Y$26),$C19,MATCH(D$18,$V$3:$Y$3,0),MATCH($D$2,$U$29:$U$31,0)))),"",INDEX(($V$4:$Y$10,$V$12:$Y$18,$V$20:$Y$26),$C19,MATCH(D$18,$V$3:$Y$3,0),MATCH($D$2,$U$29:$U$31,0)))

Threats
=IF(OR(ISERROR(INDEX(($V$4:$Y$10,$V$12:$Y$18,$V$20:$Y$26),$C19,MATCH(K$18,$V$3:$Y$3,0),MATCH($D$2,$U$29:$U$31,0))),ISBLANK(INDEX(($V$4:$Y$10,$V$12:$Y$18,$V$20:$Y$26),$C19,MATCH(K$18,$V$3:$Y$3,0),MATCH($D$2,$U$29:$U$31,0)))),"",INDEX(($V$4:$Y$10,$V$12:$Y$18,$V$20:$Y$26),$C19,MATCH(K$18,$V$3:$Y$3,0),MATCH($D$2,$U$29:$U$31,0)))

The only difference in the four formulas is the reference to the Area title (D4, D18, K4, K18). To accommodate this though I have changed the columns headers in V3:Y3 to the whole words rather than just initial letters.
0
Rob HensonFinance AnalystCommented:
Thinking about it, the results will be one of three options:

1) Required Text
2) An Error as outside the chosen candidate's area
3) Blank but blank will show as zero

Rather than making a lengthy formula to allow for blank not showing as zero, you can switch off displaying zeros just for that sheet.

The formula can then be just:
=IFERROR(INDEX(($V$4:$Y$10,$V$12:$Y$18,$V$20:$Y$26),$C5,MATCH(D$4,$V$3:$Y$3,0),MATCH($D$2,$U$29:$U$31,0)),"")

Adjust the D$4 to the various area headers.

To hide zeros, go to File > Options > Advanced in left hand pane. Scroll about 3/4 of the way down the right hand pane where there is "Display Options for this worksheet:" and a list of Sheet names, if the cursor is in on the SWOT sheet then that should already be selected otherwise select it from the list.

In the list of options below that header there is: "Show a zero in cells that have zero value". Untick this option.
0
Jase AlexanderCompliance ManagerAuthor Commented:
Hi Rob

Thank you so much for the additional information

Cant thank you enough for the help

Jase
0
Rob HensonFinance AnalystCommented:
Glad to be of help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.