formula to find 3 filled values in range 2

[robertsquestion]

Hi,

I'm using an Excel-2003 spreadsheet with 20 columns:
A: Productname
B: Quantity
C: Productname
D: Quantity
E: Productname
F: Quantity
etc. untill column T.

The Productname-columns contain Text and the Quantity-columns contain Numbers.
On every row, a maximum of 3 Productname columns are filled (so there could be zero, one, two or three Productnames filled on any row).
Now the question is: in columns U, V and W we would like to see the productnames that are filled on that row. So if no productnames are filled on that row, columns U, V and W should also be empty. And if there's just one productname filled, there should only be a value in column U etc.

I hope this explanation is clear. I've tried some array formula's, but that didn't work because of the mixture of text/numbers on a row and because of empty cells.

I hope someone out there can help me out!
Thanks in advance for your help.

Robert
The Netherlands

 

[Andrzejek]

In Cell U1 put =A1, in V put =C1, and in W put =E1
Copy cells UVW and paste it down the rows.


Have fun.

---- Andy

 

[robertsquestion]

Hi Andy,

Thanks for your reply, but this is not solving it. Let me explain a bit more:
It could be that cells A1, G1 and K1 are filled with a Productname.
Or it could be that cells C1, E1 and G1 are filled with a Productname.
Or it could be that cells C1, K1 are filled with a Productname.

So I don't know in advance in which cells the maximum of 3 productnames are filled.

So I need the 3 productnames in the 3 columns U, V and W, regardless where the productnames on that row are in. And it could also be that there are only 1 or 2 (or none) productnames filled.

Any idea how to solve this?

Thanks,
Robert

 

[SkipVought]

Hi,

FYI, this is an absolutely horrible table, unless there is some very specific unstated requirement, in which case the table design for this 3 columns of 2 is dredful as well.

Is/are the productname placement name(s) on the row significant? In other words, does it matter what row or column the productname value resides in? If so, for what reason?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[robertsquestion]

Hi Skip,

Thanks for your reply. I agree that it's not a very nice table, but I have to live with it, I can't change that part.
And the placement of the productnames are significant as the Quantity-column belongs to the (previous) Productname-column.

So row 2 could look like this:
A2 = apple
B2 = 5
E2 = pear
F2 = 3
K2 = orange
L2 = 7
and the rest of the cells from row 2 are empty

Row 3 could look like this:
C2 = pineapple
D2 = 9
G2 = kiwi
H2 = 3
and the rest of the cells from row 3 are empty


So the goal is to retrieve the 3 productnames per row.

Hope this makes it a bit more clear and that you know a solution for this.

Thanks,
Robert

 

[SkipVought]

Work with us here, please

Does it matter that apple,5 is on the row with pear,3 and orange,7, or could that set of data, just as well be on antother row?
[tt]
A2 = apple
B2 = 5
E2 = pear
F2 = 3
K2 = orange
L2 = 7
[/tt]
What is the business case for this table?

Please answer both of these question.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[robertsquestion]

Hi Skip,

The data on row 2 could just as well be on another row, but they belong together. They represent, let's say, the sales from one customer.

So each row represents the sales from one customer and each customer has bought a maximum of 3 products.
So let's say row 2 is from Customer X: this customer has bought 5 apples, 3 pears and 7 oranges.
Row 3 is from customer Y etc.

In my original file, the customername is in column A. I left it out in the text above to simplify it a bit, but I guess this made it more confusing..

Finally, the result should be that I've got one worksheet where I can display the pictures from the 3 products that one customer is normally buying. So the solution in columns U, V and W I'm asking for, is the first step to prepare this..

So I'm only looking for the productnames, the qty's are not relevant although the qty's should stay in the columns mentioned above (the qty's are used in a formula on another worksheet).

Thanks,
Robert

 

[Andrzejek]

So you have a CustomerName in column A, ProductName and Quantity columns all over. Are there any other information in there? Or all other cells are empty?

Have fun.

---- Andy

 

[SkipVought]

Yes, you have given a microscopic view of your data when a macroscopic would have been more helpful!!!

My effort would be to FIRST, normalize the data, which is much, MUCH, MUCH simpler to analyze, than the dredful table that has been mercilessly inflicted upon you.

Finally, the result should be that I've got one worksheet where I can display the pictures from the 3 products that one customer is normally buying.

I am still having trouble understanding your requirement. You state that you have, it seems, TEN productname/qty column pairs for each CUSTOMER row. So that means that you could have up to TEN product/qty values for the CUSTOMER, but NEVER ANY MORE. Is that correct? Then you want THREE products displayed. What is the logic for determinint which THREE out of the possible TEN?




Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[robertsquestion]

Hi Andy,

So let's move the data one column, and indeed put the customername in column A.
The productnames are (can be filled) in columns B, D, F, H, J, L, N, P, R, T
The qty's are (can be filled) in columns C, E, G, I, K, M, O, Q ,S, U

On each row, a customername is in column A.
If a productname is filled on a row, the qty of that product is in the next column. So B and C belong together, D and E belong together etc.
So on each row, there are maximum 7 cells filled (and the other cells on that row are empty!):
one customername, 3 productnames and 3 qty-fields.

Hope this is clear.

Thanks,
Robert

 

[robertsquestion]

Hi Skip,

Sorry, just read your last reply. Maybe my last addition has answered your last questions.
So:
"So that means that you could have up to TEN product/qty values for the CUSTOMER, but NEVER ANY MORE. Is that correct?"
Answer: There are indeed TEN possible product/qty combinations(columns), but one customer can never order more than 3 different product-names. (so a maximum of 3 product/qty columns are filled per customer/row)

"What is the logic for determinint which THREE out of the possible TEN?"
Answer: a customer only orders (max) 3 different product-names.

Hope this answers your questions.

Regards,
Robert

 

[SkipVought]

So your FIRST statement was bogus?

I'm using an Excel-2003 spreadsheet with 20 columns:

Please post an example for 2 companies, both the SOURCE data and the RESULT data. Please post a COPY 'n' PASTE example, NOT the type that you have been posting.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[robertsquestion]

Hi Skip,

I hope this media-fire link below is working, never used it before..
So the source columns are from columns A - U.
The result-columns are in columns W - Y.

Hope it's clear like this.

Regards,
Robert

 

[Rofeu]

Try this:

Column W: 0
Column X,Y,Z (arrayformula):

{=MIN(IF(($B1:$V1<>"")*EVEN(COLUMN($B1:$V1))*(COLUMN($B1:$V1)>W1),COLUMN($B1:$V1),25))}

Column AA,AB,AC:

=OFFSET($A1,0,X1-1)

Cheers,

Rofeu

 

[SkipVought]

Many of us have company security restrictions that prevent us from viewing such downloads.

1) NORMALIZE you data using the process outlined in faq68-5287

2) In your hew normalized table, DELETE the rows containing quantities and no product name.

3) Change the Column Headings to
[tt]
Customer Field FldVal

[/tt]
3) if your Excel version is 97-2003, Name your ranges using Insert > Name > Create name in TOP row.

4) Create a UNIQUE list of Customers, using Advanced Filter for instance, or Data > Remove Duplicates or MS Query.

5) Assuming that your unique list of Customers is in column F like this...
[tt]
Customer 1 2 3
abc
xyz
[/tt]
the formula in G2 would be in 2007+...
[tt]
G2: =IFERROR(INDEX(Table1[FldVal],MATCH($F2,Table1[Company],0)+G$1-1,1),"")
[/tt]
the formula in G2 would be in 97-2003...
[tt]
G2: =IFERROR(INDEX(FldVal,MATCH($F2,Company,0)+G$1-1,1),"")
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

This formula will work better, using the OFFSET() function to define the company range...
[tt]
=INDEX(OFFSET($A$1,MATCH($F2,Table1[Company],0),2,COUNTIF(Table1[Company],$F2),1),G$1,1)
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[robertsquestion]

Hi Rofeu,

I've tested your solution. I can follow the logic of the array-formula. But the outcome is unfortunately not completely correct yet.
So I've pasted the array-formula to X, Y and Z.
And I've pasted the 'offset' formula to AA, AB and AC.

The problem is that the array-formula also returns the qty-values, while it should only check/return the productname-values.

So for example at row 2 in my example file it now returns:
X2 = 2
Y2 = 3 (this is a qty-column)
Z2 = 8
AA = apple
AB = 3 (this is the qty belonging to 'apple')
AC = pear

It should have returned:
X2 = 2
Y2 = 8
Z2 = 14
AA = apple
AB = pear
AC = pineapple

I've uploaded a new excel file, see link below.
Sheet 1 contains the original example, sheet 2 contains the version with the array and offset-formula's.

Do you think that the array-formule can be changed so that it returns only the productnames?
Would be great if you can get that working, appreciate your help!

Thanks,
Robert

 

[robertsquestion]

Hi Skip,

I can follow your suggestions, but it takes quite some manual steps to finally get to the result. And it's not a one-time job, there will be a lot of versions of this workbook in use at different places. So it would be quite time-consuming to perform all these steps over and over again.

So I would prefer a 'formula-solution' as Rofeu is suggesting here. But I realize that it's not an easy one as the data is not really 'clean'. But I hope that there's some sort of a solution and really appreciate your suggestions!

Thanks,
Robert

 

[SkipVought]

Rather than the PivotTable process, you could use MS Query, joining THREE queries with a UNION ALL. That QueryTable only needs to be Refreshed, one step, and it can be part of a macro to 'automate' the process.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Rofeu]

Hi, Robert,

sorry, the function EVEN doesn't do what I assumed it to do.

Try this instead:

{=MIN(IF(($B1:$V1<>"")*(MOD(COLUMN($B1:$V1),2)=0)*(COLUMN($B1:$V1)>W1),COLUMN($B1:$V1),25))}

Cheers,

Rofeu