Formula to display cell based on multiple critera

[Rygor]

Hi,

Hoping someone can help me create a formula to solve a problem I am having.

I have an output dump of data which is in a format similar to below. Column A has the "site" ID which can appear multiple times. Column B is a subheading (only interested in lines with "Issue" and Column C has the "Issue".

Site Description Issue
1111 Data
1111 Issue Issue A
1111 Issue Issue B
2222 Issue Issue C
2222 Issue Issue D
2222 Data

On a second sheet, I want to create another spreadsheet which sorts this data into one line per site. I want it so there will be Column A - Site Number, Column B - Issue 1, Column B, Issue 2.

Site Issue 1 Issue 2
1111 Issue A Issue B
2222 Issue C Issue D


I've attached an example spreadsheet with what I am trying to do. The "Summary" sheet I am trying to find a formula to use in the orange cells (B2-C4) which will list all the issues.

Thanks for any assistance.

[canapone]

Hi,

In B2 to be copied across

Please Login or Register  to view this content.

Do you need to list the sites in column A of sheet "summary"?

[NickyC]

Hi
this macro should do it. It assumes the following named ranges:

• "sites" = the range on the data sheet containing the site list (A2:A7 on your current sheet)
• "issues" = the range on the data sheet containing the issues (C2:C7 on your current sheet)
• "sitelist" = the range on the summary sheet containing the names of all sites (A2:A3 on your current sheet)

Please Login or Register  to view this content.

[canapone]

Hi Again

in A2 to be copied below

Please Login or Register  to view this content.

Please refer to the attachment.

--------------

Regards

[AliGW]

Hi
this macro should do it.

Please note that the OP has asked for a formula solution.

[NickyC]

oops - so they did
looks like canapone's answer fits the bill

[Rygor]

Thanks Canapone. That formula does the trick - I haven't seen the aggregrate command before - something new to learn. Thanks again

[Rygor]

Just realised I need to add another level of complexity to the equation. A Site number may have multiple visit dates, so I will have multiple columns for Issues on Visit 1 and Issues for Visit 2 etc.
Is there a way to amend the formula to put any issues with Site "A" and date "x" under visit 1 issues, Site "A" and "Date y" under Visit 2 issues etc?

Data Dump:
Site Date Description Issue
1111 1/01/2017 Data
1111 1/01/2017 Issue Issue A
1111 1/01/2017 Issue Issue B
1111 2/02/2017 Issue Issue C
2222 1/01/2017 Issue Issue WW
2222 2/02/2017 Issue Issue XX
2222 2/02/2017 Data


Example.JPG
Thanks in advance.

[canapone]

Hi,

you could use an helper row (Row1) to split dates

in B1

=AGGREGATE(15,6,Data!$B$2:$B$100/(Data!$B$2:$B$100>MAX($A1:A1)),1)


In B3 to be copied across, the new INDEX exploits information in row 1

=IFERROR(INDEX(Data!$D$1:$D$1000,AGGREGATE(15,6,ROW($1:$992)/((Data!$D$1:$D$1000<>"")*(Data!$A$1:$A$1000=$A3)*(Data!$B$1:$B$1000=MAX($B$1:B$1))),RIGHT(B$2))),"")


There for sure better approaches

[Rygor]

Thanks Canapone. I was on the same thought pattern and had already added extra columns with the visit date - just couldn't get the formula working with it. Your one works great. Thank you again!