Excel Xlookup + Filter Combination

[swill85]

0
i have a CSV file containing various data that i have set a macro up to run the following formula for in a second sheet
(For reference the CSV file might contain 20 items [aswell as other data i dont need], all of which will have a Name, Serial Number and Location)
=FILTER(Sheet1!$F:$F &"",Sheet1!$C:$C=A1, "")

where A1 would be equal to "Item Name"

Item Name	Serial Number	Location	Door Type
(Filter goes here)	(Filter goes here)	(Filter goes here)	(Filter goes here)

i have a piece of text in A1 called "Item Name" - my formula effectively searches column C in sheet one for the word "Item Name" and then filters out all the answers to that question from column F.
this works perfectly for all the questions that are asked every time and lists out the data i require.

The issue i have some questions are only asked on some of the items, and although it picks out the answers, it doesnt put them into the correct rows of items (just lists them from the next row down)
i was wondering if i could combine some statements or nest an xlookup to say, first look for the Item Name, and then return the next answer to this question
EG: look for Item name, and then return the answer to the question in cell D1

i dont mind if its a nested xlookup, or an if and filter combination, or any method as long as it returns it in the correct row

i have tried an if function combined with filter, but this results in a #SPILL! message

=IF(Answer=A2,(FILTER(Answer &"",Question=D1,"")),"Not Found")

ive also tried the opposite way

=FILTER(Answer&"",IF(Question=D1&Answer=A2,"Answer","False"),"Not Found")

ive also tried a nested xlookup

=XLOOKUP(A2, AnswerF:F, XLOOKUP(D2,QuestionC:C,AnswerC:C,"")) where A2 is the item name, and D2 is the new question

im happy to share the file with anyone if this helps and i hope i have explained myself properly.

thanks for any help with what i am doing wrong

Column C	Column F
(Question)	(Answer)
Items 1
Item Name	abcd
Serial Number	123456
Location	Kitchen
Items 2
Item Name	abcde
Serial Number	1234567
Location	Kitchen
Items 3
Item Name	abcdef
Serial Number	12345678
Location	Kitchen
Door Type	Wooden

 

[KRice]

Have you considered transforming the initial table using Power Query to pivot the data into the appropriate columns? Starting with a table like this (select the table area, hit Ctrl-t and indicate that the table has headers...assuming column headings of Question, blank, and Answer) to establish the initial table range as a formal Excel table. While selecting a cell in the table, click on the Table Design menu group and note the name of the table in the upper left (in my example, the table is called Table1). Then click Data > From Table/Range while a cell somewhere in the table is selected. This opens Power Query (PQ). In PQ, click on View > Advanced Editor and paste the following M code into the editing window:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Columns1" = Table.RemoveColumns(Source,{"Column1"}),
    #"Added Conditional Column" = Table.AddColumn(#"Removed Columns1", "Group", each if Text.StartsWith([Question], "Items") then [Question] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Group"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Answer] <> null)),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Question]), "Question", "Answer"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Group"})
in
    #"Removed Columns"

In the M Code, 1st line below "Source", change the name of the table to match yours (Table1 --> to your table name). In the next line, change the name of the unused column D (has a heading of Column1 in my example) to the name that matches your unused column. Then click Done...the code should pivot the information into the format sought.

Book7
	A	B	C	D
1	Item Name	Serial Number	Location	Door Type
2	abcd	123456	Kitchen
3	abcde	1234567	Kitchen
4	abcdef	12345678	Kitchen	Wooden
Table1

The data table is a formal table that will expand and collapse as records are added or deleted from it. The transformed table can be refreshed to reflect changes in the source data by re-running the PQ script with Data > Refresh All.

 

[RudRud]

Book2
	C	D	E	F	G	H	I	J	K	L	M
1	(Question)	(Answer)		Item name	Serial Number	Location	Door Type			Item Name	1
2	Items 1			abcd	123456	Kitchen				Serial Number	2
3	Item Name	abcd		abcde	1234567	Kitchen				Location	3
4	Serial Number	123456		abcdef	12345678	Kitchen	Wooden			Door Type	4
5	Location	Kitchen
6	Items 2
7	Item Name	abcde
8	Serial Number	1234567
9	Location	Kitchen
10	Items 3
11	Item Name	abcdef
12	Serial Number	12345678
13	Location	Kitchen
14	Door Type	Wooden
Sheet1

I normally clean the data with Column L:M Helper (Define Column Name with column number)

Result in F:I Columns . FYR

Option Explicit
Sub test()
Dim ws As Worksheet
Dim i%, k%
Set ws = Sheets("sheet1")
Dim dict As Object
Dim a()
ReDim b(1 To 10000, 1 To 4)
Set dict = CreateObject("Scripting.Dictionary")
dict.Comparemode = vbTextCompare

'Helper L:M Put dictionary column
a = ws.Range("l1:m" & ws.Cells(Rows.Count, "l").End(xlUp).Row).Value

For i = 1 To UBound(a, 1)
    If Not dict.exists(a(i, 1)) Then
        dict.Add a(i, 1), i
    End If
Next i

'Loop through each key dict name
a = ws.Range("c2:d" & ws.Cells(Rows.Count, "d").End(xlUp).Row).Value

For i = 1 To UBound(a, 1)
    If dict.exists(a(i, 1)) Then
       If a(i, 1) = "Item Name" Then k = k + 1
        b(k, dict(a(i, 1))) = a(i, 2)
    End If
Next i

With ws
    .[f1:i5000].ClearContents
    .[f1] = "Item name"
    .[g1] = "Serial Number"
    .[h1] = "Location"
    .[i1] = "Door Type"
    .[f2].Resize(UBound(b, 1), UBound(b, 2)).Value = b
End With

End Sub