# need help with my problem

#### SHAHID_XL

##### New Member
data
 I WANT THE FOLLOWING OUTPUT article color 4 5 6 7 8 9 10 11 12 13 1307 BR 5 1307 BR 0 2 1 1 1 0 1 1 0 0 1307 BR 5 1307 BK 0 1 1 1 2 1 1 0 0 0 1307 BR 6 1311 MR 0 0 0 1 1 1 0 0 0 1 1307 BR 7 1307 BR 8 1307 BR 10 1307 BR 11 1307 BK 5 1307 BK 6 1307 BK 7 1307 BK 8 1307 BK 8 1307 BK 9 1307 BK 10 1311 MR 7 1311 MR 8 1311 MR 9 1311 MR 13

<tbody>
</tbody>

it finds for the article and the color and searches if in corresponding colum "4" there is a zero then it does nothing then it will will check column under 5 if it says 2 it print 5 twice against the article and color

thanks in advance plz help me out with this

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
try countifs
in the output table, where the first 0 below the 4 is.
Column A contains the 1307 or 1311
Column B contains the color code
Column C contains the number

Column D contains the table article
Column E contains the table color
Column F contains 4 moving on.
In Cell F2 enter the following formula
=countifs(\$c:\$c,\$A:\$A,\$d2,\$B:\$B,e2,\$C:\$C,F\$1)
drag right
then drag down

If you don't want to go through all that, a pivot table will provide the same outcome.

try countifs
in the output table, where the first 0 below the 4 is.
Column A contains the 1307 or 1311
Column B contains the color code
Column C contains the number

Column D contains the table article
Column E contains the table color
Column F contains 4 moving on.
In Cell F2 enter the following formula
=countifs(\$c:\$c,\$A:\$A,\$d2,\$B:\$B,e2,\$C:\$C,F\$1)
drag right
then drag down

If you don't want to go through all that, a pivot table will provide the same outcome.
actually the following is the data i want to plug values i will repost
 ARTICLE COLOR 4 5 6 7 8 9 10 11 12 13 1307 BR 0 2 1 1 1 0 1 1 0 0 1307 BK 0 1 1 1 2 1 1 0 0 0 1311 MR 0 0 0 1 1 1 0 0 0 1 i want the following result i have got a way how to get article and the color but need a way how to put the numbers THE NUMBERS AS U CAN SEE IF 1307 BK 4 THE NUMBER OF PAIRS IS ZERO THEN IT SHOULD CHECK 1307 BK 5 IT SHOWS NUMBER OF PAIRS OF SIZE 5 ARE TWO SO IT WRITES 5 TWICE IN C16 AND C17 THEN IN C18 AS 1307 BK 6 IS 1 PAIR SO IT SHOULD WRITE 6 ONCE IN C18 AND SO ON IT SHOULD POPULATE LIST 1307 BR 5 1307 BR 5 1307 BR 6 1307 BR 7 1307 BR 8 1307 BR 10 1307 BR 11 1307 BK 5 <----- I WANT THIS RESULT WITH THE DATA WHICH IS PROVIDED ON TOP 1307 BK 6 1307 BK 7 1307 BK 8 1307 BK 8 1307 BK 9 1307 BK 10 1311 MR 7 1311 MR 8 1311 MR 9 1311 MR 13

<colgroup><col span="15"></colgroup><tbody>
</tbody>

plz help me out

Paste your as it is data of article color in A1 and run this code result will come in Q,R,S column

Sub t()
x = 1
With ActiveSheet
For nrow = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
For i = 3 To 13
temp = Cells(nrow, i)
If temp <> 0 Then
Range(Cells(nrow, 1), Cells(nrow, 2)).Copy
Range("Q" & x).Resize(temp, 2).PasteSpecial xlPasteAll
Range("S" & x).Resize(temp, 1).Value = Cells(1, i)
x = x + temp
End If
Next
Next
End With
End Sub

Paste your as it is data of article color in A1 and run this code result will come in Q,R,S column

Sub t()
x = 1
With ActiveSheet
For nrow = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
For i = 3 To 13
temp = Cells(nrow, i)
If temp <> 0 Then
Range(Cells(nrow, 1), Cells(nrow, 2)).Copy
Range("Q" & x).Resize(temp, 2).PasteSpecial xlPasteAll
Range("S" & x).Resize(temp, 1).Value = Cells(1, i)
x = x + temp
End If
Next
Next
End With
End Sub

thanks but can i get it in formula instead of vb script its not executing giving sum runtime 1004

 article colour 4 5 6 7 8 9 10 11 12 13 1307 BR 0 2 1 1 1 0 1 1 0 0 1307 BK 0 1 1 1 2 1 1 0 0 0 1311 MR 0 0 0 1 1 1 0 0 0 1 raw data above i want the results beow after putting formula 1307 BR 5 1307 BR 5 1307 BR 6 1307 BR 7 1307 BR 8 1307 BR 10 1307 BR 11 1307 BK 5 1307 BK 6 1307 BK 7 1307 BK 8 1307 BK 8 1307 BK 9 1307 BK 10 1311 MR 7 1311 MR 8 1311 MR 9 1311 MR 13

<colgroup><col><col span="11"></colgroup><tbody>
</tbody>

Is that the full width of your data, or do you have more columns?

I GOT A PRICE COLUMN AND A DATE COLUMN AFTER 13 WHICH I WANT TO BE LISTED IN THE SORTED COLUMN BELOW

 article colour 4 5 6 7 8 9 10 11 12 13 PRICE DATE PURCHASED 1307 BR 0 2 1 1 1 0 1 1 0 0 999 04-12-2015 1307 BK 0 1 1 1 2 1 1 0 0 0 10000 04-01-2016 1311 MR 0 0 0 1 1 1 0 0 0 1 11111 01-05-2013 raw data above i want the results beLow after putting formula 1307 BR 5 999 04-12-2015 1307 BR 5 999 04-12-2015 1307 BR 6 999 04-12-2015 1307 BR 7 999 04-12-2015 1307 BR 8 999 04-12-2015 1307 BR 10 999 04-12-2015 1307 BR 11 999 04-12-2015 1307 BK 5 10000 04-01-2016 1307 BK 6 10000 04-01-2016 1307 BK 7 10000 04-01-2016 1307 BK 8 10000 04-01-2016 1307 BK 8 10000 04-01-2016 1307 BK 9 10000 04-01-2016 1307 BK 10 10000 04-01-2016 1311 MR 7 11111 01-05-2013 1311 MR 8 11111 01-05-2013 1311 MR 9 11111 01-05-2013 1311 MR 13 11111 01-05-2013

<tbody>
</tbody>

What you want is available with Visual basic, but I do not believe it is available by formula. The VB code provided below worked perfectly for me. Cout it be that you do not have the data in the same columns as the code. Try adjusting the code to meet the table requirements.

Replies
2
Views
96
Replies
7
Views
346
Legacy 143009
L
Replies
12
Views
179
Replies
1
Views
67
Replies
2
Views
91

1,216,105
Messages
6,128,859
Members
449,472
Latest member
ebc9

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back