Vlookup Hlookup with blank values

[SSJ_CHRIS]

​

I am looking for a formula to return some values.
I have a list of customer names in B2:B300 (ex: john, Mary zack) ,
Items ordered in the columns A2:A8 (ex: shirt, shoes, jacket)

Shirt. Shoes. Jacket

John. "blank" 3. 2

Mary. 6. "blank" 5

Zack. "blank" "blank" 1


I want to lookup each customer and return the following to a different sheet


John. Shoes. Jacket
3. 2

Mary. Shirt. Jacket
6. 5

Zack. Jacket
1

 

[LxQ]

OK, so if I'm interpreting this correctly, John ordered 3 shoes and 2 jackets..

do the items have to be in A2:A8 or can they be headings of columns, such as C1:I1?

--how do you want the second sheet again?

 

[SSJ_CHRIS]

They can be in the be C1:I8, the items can be column headers.

I have edited my first post to reflect the change.
*Vlookup Hlookup with blank values

I am looking for a formula to return some values.*

I have a list of customer names in B2:B300 (ex: john, Mary zack) ,
Items ordered in the columns C1: C8 (ex: shirt, shoes, jacket)

- - - - - - - - Shirt----- Shoes------- Jacket
John--------"blank"-----3------------- 2
Mary---------6--------"blank"----------5
Zack------- "blank"----"blank"----------1

I want to lookup each customer and return the following to a different sheet*John. Shoes. Jacket3. 2Mary. Shirt. Jacket6. 5Zack. Jacket1

 

[LxQ]

OK, so let's start with John.. Cell A1 would say John, Cell B1 would say Shoes? or all of "John. shoes. Jacket3.2" would be in A1?

 

[SSJ_CHRIS]

[
QUOTE=LxQ;3487638]OK, so let's start with John.. Cell A1 would say John, Cell B1 would say Shoes? or all of "John. shoes. Jacket3.2" would be in A1?[/QUOTE]

-------A------------B--------------C--------------D
1------------------Shoes---------Jacket
2-----John - - - - - - 3-------------2
3-----------------Shirt-----------Jackey
4-----Mary---------6--------------2
5------------------Jacket------------
6-----Zack----------1------------

Sorry for the formatting; the B1 would return the first item john ordered and C1 would returnrthe next item ordered. The quantity of each item ordered would put below the item in B2 and C2.

All customers in column A are static and all unique, a Match could be used.

 

[LxQ]

I'm afraid I'll have to say that I don't know... maybe if you want to use some extra columns that you can hide later, or even an extra worksheet where you compile the data.. or VBA...

this is what I have for B1 and it only gets more complicated from than on:

=LEFT(IF(VLOOKUP($A2,Sheet1!$A$2:$I$4,2,)=0,"",Sheet1!$B$1&" ")&IF(VLOOKUP($A2,Sheet1!$A$2:$I$4,3,)=0,"",Sheet1!$C$1&" ")&IF(VLOOKUP($A2,Sheet1!$A$2:$I$4,4,)=0,"",Sheet1!$D$1&" ")&IF(VLOOKUP($A2,Sheet1!$A$2:$I$4,5,)=0,"",Sheet1!$E$1&" ")&IF(VLOOKUP($A2,Sheet1!$A$2:$I$4,6,)=0,"",Sheet1!$F$1&" ")&IF(VLOOKUP($A2,Sheet1!$A$2:$I$4,7,)=0,"",Sheet1!$G$1&" ")&IF(VLOOKUP($A2,Sheet1!$A$2:$I$4,8,)=0,"",Sheet1!$H$1&" ")&IF(VLOOKUP($A2,Sheet1!$A$2:$I$4,9,)=0,"",Sheet1!$I$1&" "),FIND(" ",IF(VLOOKUP($A2,Sheet1!$A$2:$I$4,2,)=0,"",Sheet1!$B$1&" ")&IF(VLOOKUP($A2,Sheet1!$A$2:$I$4,3,)=0,"",Sheet1!$C$1&" ")&IF(VLOOKUP($A2,Sheet1!$A$2:$I$4,4,)=0,"",Sheet1!$D$1&" ")&IF(VLOOKUP($A2,Sheet1!$A$2:$I$4,5,)=0,"",Sheet1!$E$1&" ")&IF(VLOOKUP($A2,Sheet1!$A$2:$I$4,6,)=0,"",Sheet1!$F$1&" ")&IF(VLOOKUP($A2,Sheet1!$A$2:$I$4,7,)=0,"",Sheet1!$G$1&" ")&IF(VLOOKUP($A2,Sheet1!$A$2:$I$4,8,)=0,"",Sheet1!$H$1&" ")&IF(VLOOKUP($A2,Sheet1!$A$2:$I$4,9,)=0,"",Sheet1!$I$1&" "),1)-1)

 

[SSJ_CHRIS]

I've been searching so much for an answer before I posted and everything seems so complicated to get to the results I need. If anyone else can find an "elegant" formula or vba code would be much appreciated. LxQ thank u for your time and getting me to articulate what I need so I can get the help I need.

 

[LxQ]

well.. so my idea was to build a string of characters from the column headings with a conditional formula of omitting it if there was no particular product ordered, then split the string... so what if you use the same idea and build a 30 character string for each product that was ordered?
The first one would be:
IF(VLOOKUP($A2,Sheet1!$A$2:$I$4,2,)=0,"",LEFT(Sheet1!$B$1&"- - - - - - - - - - - - - - - ",30)) where the - - - is actually 30 spaces..

the next column instead of Sheet1!$A$2:$I$4,2, would be Sheet1!$A$2:$I$4,3,
and the heading would from Sheet1!$C$1 instead of Sheet1!$B$1
You build this whole string together, then for the first column heading you take the first 30 characters and use a TRIM function to lose the extra spaces:

=TRIM(LEFT(IF(VLOOKUP($A2,Sheet1!$A$2:$I$4,2,)=0,"",LEFT(Sheet1!$B$1&" ",30))&IF(VLOOKUP($A2,Sheet1!$A$2:$I$4,3,)=0,"",LEFT(Sheet1!$C$1&" ",30))&IF(VLOOKUP($A2,Sheet1!$A$2:$I$4,4,)=0,"",LEFT(Sheet1!$D$1&" ",30))&IF(VLOOKUP($A2,Sheet1!$A$2:$I$4,5,)=0,"",LEFT(Sheet1!$E$1&" ",30))&IF(VLOOKUP($A2,Sheet1!$A$2:$I$4,6,)=0,"",LEFT(Sheet1!$F$1&" ",30))&IF(VLOOKUP($A2,Sheet1!$A$2:$I$4,7,)=0,"",LEFT(Sheet1!$G$1&" ",30))&IF(VLOOKUP($A2,Sheet1!$A$2:$I$4,8,)=0,"",LEFT(Sheet1!$H$1&" ",30))&IF(VLOOKUP($A2,Sheet1!$A$2:$I$4,9,)=0,"",LEFT(Sheet1!$I$1&" ",30)),30))

for the next heading instead of using a LEFT(..., 30) you can use a MID(..., 31, 30) to start at character 31 and go another 30 spaces

=TRIM(MID(IF(VLOOKUP($A2,Sheet1!$A$2:$I$4,2,)=0,"",LEFT(Sheet1!$B$1&" ",30))&IF(VLOOKUP($A2,Sheet1!$A$2:$I$4,3,)=0,"",LEFT(Sheet1!$C$1&" ",30))&IF(VLOOKUP($A2,Sheet1!$A$2:$I$4,4,)=0,"",LEFT(Sheet1!$D$1&" ",30))&IF(VLOOKUP($A2,Sheet1!$A$2:$I$4,5,)=0,"",LEFT(Sheet1!$E$1&" ",30))&IF(VLOOKUP($A2,Sheet1!$A$2:$I$4,6,)=0,"",LEFT(Sheet1!$F$1&" ",30))&IF(VLOOKUP($A2,Sheet1!$A$2:$I$4,7,)=0,"",LEFT(Sheet1!$G$1&" ",30))&IF(VLOOKUP($A2,Sheet1!$A$2:$I$4,8,)=0,"",LEFT(Sheet1!$H$1&" ",30))&IF(VLOOKUP($A2,Sheet1!$A$2:$I$4,9,)=0,"",LEFT(Sheet1!$I$1&" ",30)),31,30))

I apologize for the long formula.. if I ever messed up a formula and wrote something very long where it doesn't need to be, then this might be it...

Edit: I thought this was going to be an issue.. where I have 30 spaces, it shows only 1

 

[SSJ_CHRIS]

Is a formula possible with the use of a MATCH and where I can call the formula to look in that row where the customer name is found and whe it searches the range for a value larger than 0 it moves to the first cell at the top of the sheet (column header) and pulls that down into my designated location. I think pulling the second part of my request, the quantity ordered, would be a simply Vlookup formula.

Any thoughts of how I can do this?

 

[SSJ_CHRIS]

I keep trying things and can't seem to get a formula to go to the top of the column and get the header name. Seems so easy but what am I missing?