need help with my problem

SHAHID_XL

New Member
Joined
Feb 17, 2016
Messages
6
data
I WANT THE FOLLOWING OUTPUT articlecolor45678910111213
1307 BR51307BR0211101100
1307 BR51307BK0111211000
1307BR61311MR0001110001
1307BR7
1307 BR8
1307BR10
1307BR11
1307BK5
1307BK6
1307BK7
1307BK8
1307BK8
1307BK9
1307BK10
1311MR7
1311MR8
1311MR9
1311MR13

<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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

dermie_72

Well-known Member
Joined
Sep 4, 2012
Messages
1,540
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. :)
 

SHAHID_XL

New Member
Joined
Feb 17, 2016
Messages
6
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 45678910111213
1307BR0211101100
1307BK0111211000
1311MR0001110001
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
1307BR5
1307BR5
1307BR6
1307BR7
1307BR8
1307BR10
1307BR11
1307BK5<----- I WANT THIS RESULT WITH THE DATA WHICH IS PROVIDED ON TOP
1307BK6
1307BK7
1307BK8
1307BK8
1307BK9
1307BK10
1311MR7
1311MR8
1311MR9
1311MR13

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

MandeepBaluja

Board Regular
Joined
Jan 28, 2014
Messages
120

ADVERTISEMENT

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
 

SHAHID_XL

New Member
Joined
Feb 17, 2016
Messages
6
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
 

SHAHID_XL

New Member
Joined
Feb 17, 2016
Messages
6

ADVERTISEMENT

articlecolour45678910111213
1307BR0211101100
1307BK0111211000
1311MR0001110001
raw data above
i want the results beow after putting formula
1307BR5
1307BR5
1307BR6
1307BR7
1307BR8
1307BR10
1307BR11
1307BK5
1307BK6
1307BK7
1307BK8
1307BK8
1307BK9
1307BK10
1311MR7
1311MR8
1311MR9
1311MR13

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

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
Is that the full width of your data, or do you have more columns?
 

SHAHID_XL

New Member
Joined
Feb 17, 2016
Messages
6
I GOT A PRICE COLUMN AND A DATE COLUMN AFTER 13 WHICH I WANT TO BE LISTED IN THE SORTED COLUMN BELOW

articlecolour45678910111213PRICEDATE PURCHASED
1307BR021110110099904-12-2015
1307BK01112110001000004-01-2016
1311MR00011100011111101-05-2013
raw data above
i want the results beLow after putting formula
1307BR599904-12-2015
1307BR599904-12-2015
1307BR699904-12-2015
1307BR799904-12-2015
1307BR899904-12-2015
1307BR1099904-12-2015
1307BR1199904-12-2015
1307BK51000004-01-2016
1307BK61000004-01-2016
1307BK71000004-01-2016
1307BK81000004-01-2016
1307BK81000004-01-2016
1307BK91000004-01-2016
1307BK101000004-01-2016
1311MR71111101-05-2013
1311MR81111101-05-2013
1311MR91111101-05-2013
1311MR131111101-05-2013

<tbody>
</tbody>
 

dermie_72

Well-known Member
Joined
Sep 4, 2012
Messages
1,540
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,048
Messages
5,639,769
Members
417,111
Latest member
buyukbang

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top