0

I have a column (call it A) in Excel lists off different items and their properties. Every item has the word "Type" associated with it and so there is a cell with that label in column A so that in the column beside it (B) the type can be listed. Some (not all) items also have a "Widget" label associated with them.

Since each item only has one "Type" label I would like to be able to do is have Excel search for the "Type" label and then check whether there is a "Widget" label BEFORE the next "Type" label, and if there is paste the value in column B adjacent to the "Type" label in another sheet. It would continue doing this until no more instances of the word "Type" were found. Is this something that's feasible to implement? To make the layout more clear a sample column is below. Note that the number of spaces between each entry, unfortunately, is not consistent and so cannot be used as a way to track where one is. Similarly, the word Widget does not have to be directly above Type, it can appear anywhere between the two instances of "Type" that bookend it.

**Type**  01
Width     .5
Length    .4
Height    .3
Weight    15
Widget    Blue
**Type**  072
Width      .1 
Length     .1
Height     .1
Weight     50
**Type**   025
Width      .4
Length     .4
Widget     Red
Weight     11
Height     .4

The desired output on the separate sheet would then simply be:

01
025

since those are the types that have a widget associated.

| improve this question | | | | |
  • I'm concerned that your sample data has been redacted too far. Is **Type** literally repeated several times as a category delimiter or has that label been homogenized from different types in the actual data? If the latter, what uniquely distinguishes each type from the other entries? – user385793 Nov 20 '14 at 21:41
  • So the objective is for the output table to show Type 01 Blue in three contiguous columns? (Apparently, the asterisks around Type cause it to show bold in the comments.) – fixer1234 Nov 20 '14 at 23:30
  • @fixer1234: You know that **text** is Markdown for text, don't you? You can get **Type** with backticks, or **Type** by typing a backslash before each *. – G-Man Says 'Reinstate Monica' Nov 21 '14 at 0:04
  • @G-Man - Thanks. I knew about the markdown. Wasn't aware that you can defeat it with backticks or backslashes. – fixer1234 Nov 21 '14 at 0:08
  • Trying again: So the objective is for the output table to show **Type** 01 Blue in three contiguous columns? – fixer1234 Nov 21 '14 at 0:09
1

Updated formula. Finds the cell with the first type and second type and looks for the word widget between it. If it exists return the value beside type if not return blank.

=IFERROR(IF(MATCH("widget",INDIRECT("A" & SMALL(IF(A:A="type",ROW(A:A),1000000),E1) & ":A" &                
SMALL(IF(A:A="type",ROW(A:A),1000000),E1+1)),0)>0,INDIRECT("B" & 
SMALL(IF(A:A="type",ROW(A:A),1000000),E1)),""),"")

Entered with ctrl+shift+enter

Cell E1 would have the number 1, E2 would have number 2. Copy the formula down to return each instance of WIDGET. Will error if there isn't anymore so you could use an iferror around it.

Would probably take a while to calculate (or my comp is just slow due to doing other things right now). and you may be better off using a macro.

Below is a very quick attempt. It lists them from the bottom up. Basically it cycles from the last row to the first. If it finds widget it turns a flag on. If the flag is on and it finds a type it returns the cell next to it.

*Edited the macro to work with finding type or widget in the string and not just an exact match

Sub get_types()

Dim lRow As Long, cRow As Long, nRow As Long
Dim FindType As Boolean

FindType = False
nRow = 2

With Sheets("sheet1")
    lRow = .Range("A" & .Rows.Count).End(xlUp).Row

    For cRow = lRow To 1 Step -1
        If InStr(UCase(.Range("A" & cRow).Value), "WIDGET") > 0 Then FindType = True
        If FindType And InStr(UCase(.Range("A" & cRow).Value), "TYPE") > 0 Then
            .Range("E" & nRow).Value = .Range("B" & cRow).Value
            FindType = False
            nRow = nRow + 1
        End If
    Next
End With

End Sub

Output, blue cells should get listed, red cell gets skipped because it doesn't have a widget. Cells listed in reverse order due to direction of the loop.

enter image description here

| improve this answer | | | | |
  • If I understand correctly this will return each entry in column B associated with "widget" and not the entry in column B associated with the "Type" associated with "widget". – Max Power Nov 20 '14 at 22:12
  • Sorry I read the question wrong. I'll post an answer but it will take forever if the data set is fairly big. It will also leave blank rows if there are types that have no widgets – gtwebb Nov 20 '14 at 23:11
  • Both of those things are definitely manageable, thanks so much. I'll take a look at the macro now. EDIT: The one thing I'm wondering is how can I write the function $ISNUMBER(SEARCH("WIDGET",A1,0)))$ in VBA? Sometimes there might be hidden characters as the data is quite messy. – Max Power Nov 20 '14 at 23:26
  • added a commented out line but you are looking for instr("Find text here", "text") which will return 0 if not found. – gtwebb Nov 21 '14 at 0:11
  • Thanks so much. One final question: An error on "Then" in your commented code results in an error "Compile Error, expected list separator." Any idea what might be causing this. I'm looking into it as I write this. – Max Power Nov 21 '14 at 17:50
0

You can do this with an IF AND statement like:

=IF(AND(A1="Widget",A2="**Type**"),B2,"")

That basically tells Excel IF the text "Widget" is in a cell above a cell with the text "**Type**", enter the data from the cell in column B next to "**Type**", otherwise, leave it blank.

You end up with this:

enter image description here

If you want the data (072 in this example) to show up in another sheet, then your formula needs to refer to the cells in their specific sheet instead. Assuming you have the data on a worksheet called "Sheet 1", use this formula on the worksheet where you want the data to appear:

=IF(AND(Sheet1!A2="Widget",Sheet1!A3="**Type**"),Sheet1!B3,"")
| improve this answer | | | | |
  • Unfortunately the data I am dealing with is slightly messier than that. For example, Widget may not be directly above Type, it is just somewhere between the two instances of Type. Because of this it isn't possible to track using A1,A2 as you have done. I should have made this more clear, sorry. – Max Power Nov 20 '14 at 18:42
  • Maybe you can help clarify by showing what you expect the output to look like. – user388043 Nov 20 '14 at 18:49
  • The issue with your formula isn't really the output, which is great, it's more that it relies on "Widget" and "Type" being a fixed width apart from each other. This assumption cannot be made with the data I have unfortunately. – Max Power Nov 20 '14 at 19:03
  • @MaxPower - is the value in C6 above supposed to be 072 or 01? – user385793 Nov 20 '14 at 21:50
  • @MaxPower It should be 01, but that's secondary to the issue of being able to check above or below for the "Type" associated with the "Widget" in the first place I think. I should have been more clear on that though. – Max Power Nov 20 '14 at 22:13
0

Here is my take on your sample data and requirements. Note that I have expanded your sample data to demonstrate how multiple scenarios are dealt with.

         Widget Types

The standard formulas in D2:G2 are,

D2 is =IF(COUNTIF(A:A,"Widget")>COUNTIF(D$1:D1,"Widget"),"Widget","") `

E2 is =IF(LEN($D2),INDEX($B$1:$B$9999,SMALL(INDEX(ROW($1:$9999)+($A$1:$A$9999<>"widget")*1E+99,,),ROW(1:1))),"")

F2 is =IF(LEN($D2),INDEX($B$1:$B$9999,SMALL(INDEX(ROW($1:$9999)+($A$1:$A$9999<>"**Type**")*1E+99,,),COUNTIF(INDIRECT("A1:A"&SMALL(INDEX(ROW($1:$9999)+($A$1:$A$9999<>"widget")*1E+99,,),ROW(1:1))),"**Type**"))),"")

G2 is =IF(LEN($D2),VLOOKUP("**Type**", INDIRECT("A"&SMALL(INDEX(ROW($1:$9999)+($A$1:$A$9999<>"widget")*1E+99,,),ROW(1:1))&":B9999"), 2, FALSE),"")

I've cut down the referenced cell ranges from full column references in order to minimize calculation lag. Both INDIRECT and INDEX (in its array form here) are considered volatile functions that recalculate whenever a calculation cycle occurs; not just when a value changes that directly affects them. Fill down as necessary.

I have made that sample workbook available for you on my OneDrive here to reference and download.

Widget_types.xlsx

That will likely keep you occupied for a bit while transcribing the formulas for your own purposes. Post back with any specific questions that you have if you run into a problem.

| improve this answer | | | | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.