Search formula

[Hillargi]

Greetings all, this is my first post--please forgive my English (and my Excel-speak).

My actual situation is more complicated than this, but suppose I have a table with 11 columns and hundreds of rows. Column A contains names of diseases, but not necessarily unique values. Columns B through J contain symptoms (eye pain, eye dryness, headache, cervical spine pain, and many more). Therefore, every row will have a disease (again, not necessarily unique), and may have between 0 and 10 associated symptoms (1 per column).

Essentially, this table represents symptoms by disease. On a separate sheet, I would like to recast these data into a table representing diseases by symptom. What I am envisioning is a column for each symptom, below which some formula would populate a list.

Assuming that there is no title row on the original page (for simplicity), and that there are 100 rows of data, the formula for the "cervical spine pain" symptom column would:

1. Search through B1:J100 (by row, for example) for instances of "cervical spine pain".
2. Upon finding an instance, it would copy the value from column A (the disease) associated with the row in which the symptom was found, and paste it below the "cervical spine pain" column of sheet two.
3. It would then continue searching, and paste new instances below the first, thereby making a list that autopopulates itself as items are added, changed, or removed from the main table (sheet 1).
4. If possible, it would then remove duplicate values from the list.

Then I want to do this with all the other symptoms.

Any idea? I've tried the Lookup family of functions, but they require unique values in Column A, and the Index and Match combination boggles me and seems not to work for this case anyway. Also, I've stalled attempting to record macros.

This seems like a fairly easy/common task, but I haven't been able to find help on it elsewhere. Any help--and of course, as soon as earthly possible, would be very appreciated.

Thanks,
Hillargi

[shg]

Welcome to the forum.

Can you post a workbook? The forum rules explain how.

[Hillargi]

Here's a simplified file.

[shg]

The same disease appears multiple times in col A

[Hillargi]

The same disease appears multiple times in col A

Exactly right. Diseases in this column are not unique. In the expanded database, the duplicates are due to differences in how the disease was acquired/contracted (another column). There are no unique identifiers, which is what makes the LOOKUP family of functions impossible in this case. What I need is a less-restrictive search-within-range, populate-a-list function, if it exists.

[shg]

Please Login or Register  to view this content.

I sorted the diseases.

The formula in H2 and copied across and down is

=IF(COUNTIF($B2:$F2, H$1), $A2, "")

[Hillargi]

Please Login or Register  to view this content.

I sorted the diseases.

The formula in H2 and copied across and down is

=IF(COUNTIF($B2:$F2, H$1), $A2, "")

Hello again,

Thanks for your help--this formula is very similar to the one I came up with. The one problem with it is that, with hundreds of rows of data, the resulting list has many, many blanks. Is there a way to remove these blanks, within the formula? Sorting each column afterward could work (since blanks would be at the bottom), I would need a way to isolate the filter to a specific column--essentially, a way to make Excel treat the columns independently. Any ideas?

Thanks again,

Hillargi

[daddylonglegs]

For your example you could use this formula in Sheet2 A2

=IFERROR(INDEX('Data Table'!$A$2:$A$50,SMALL(IF('Data Table'!$B$2:$F$50=A$1,IF(COUNTIF(A$1:A1,'Data Table'!$A$2:$A$50)=0,ROW('Data Table'!$A$2:$A$50)-ROW('Data Table'!$A$2)+1)),1)),"")

confirmed with CTRL+SHIFT+ENTER and copied across and down. When you run out of diseases for a specific symptom the formula returns blanks

No sorting is required - see attached

[Hillargi]

For your example you could use this formula in Sheet2 A2

=IFERROR(INDEX('Data Table'!$A$2:$A$50,SMALL(IF('Data Table'!$B$2:$F$50=A$1,IF(COUNTIF(A$1:A1,'Data Table'!$A$2:$A$50)=0,ROW('Data Table'!$A$2:$A$50)-ROW('Data Table'!$A$2)+1)),1)),"")

confirmed with CTRL+SHIFT+ENTER and copied across and down. When you run out of diseases for a specific symptom the formula returns blanks

No sorting is required - see attached

Wow, it's going to take me a while to figure out what exactly this code is doing. It works flawlessly in the document you attached; however, when I apply it to my actual table (with all of the columns, rows, and sheet names adjusted), it produces exactly one extra copy of each disease. For example, in Column A of the second sheet, it's coming out E E F F C C B B etc. instead of your E F C B. Any ideas what might be causing this?

Thanks again,

Hillargi

[Hillargi]

Ok, I figured out what's going on. In my actual data sheet, the Symptom name row has a description row between it and the formula, which is throwing things off. How do I adjust the formula to account for the extra row?

Hillargi

[daddylonglegs]

Sorry, I'm not clear how that looks, where would that extra row/rows be in your example sheet?

[Hillargi]

Sorry, I'm not clear how that looks, where would that extra row/rows be in your example sheet?

Apologies, I was vague. On sheet two, with the formulas, on your file--you have A through G strung along the top, with the formulae starting directly below them. On mine, the first row is the same, then the second row is full of descriptors (essential to the sheet, but not to these formulae), and the formulae begin on the third row. This is causing the formula to duplicate everything, somehow. Any ideas?

[shg]

Hillargi, please don't quote whole posts; use the REPLY button, not the QUOTE button.

Thanks.

[daddylonglegs]

OK, I see what happens - the COUNTIF function goes a little awry when you insert a row, try this version in A3, confirmed with CTRL+SHIFT+ENTER and copied across and down

=IFERROR(INDEX('Data Table'!$A$2:$A$50,SMALL(IF('Data Table'!$B$2:$F$50=A$1,IF(COUNTIF(A$2:A2,'Data Table'!$A$2:$A$50)=0,ROW('Data Table'!$A$2:$A$50)-ROW('Data Table'!$A$2)+1)),1)),"")

In general, when applied to different ranges, the first argument of the COUNTIF function refers to a single cell range - the cell immediately above

[Hillargi]

This formula works perfectly, daddylonglegs. I've applied it to my data for a couple of columns with great results, and now I'm waiting as Excel executes it for the rest of the table. I'm going to have to sit down for a while and piece through what the code is actually saying, but for now let me extend my profound appreciation for your time and efforts in helping me.

Take good care,
Hillargi