getting the lower and upper values of loop from cell

G

Ghoghnuse

New Member
Joined
May 4, 2017
Messages
12
Hello

I have a table that have IDs with the same Code And Qty .

For Example in row 1 of table , IDs from 200 to 210 have 9461626177 Code and 1000 Qty

I need a loop to get lower and upper value from cell ( Column C and D ) and copy to new table for all tale rows ( till first blank row of table)

the result will be like right table (column I to L )

Thanks in advance.

Excel 2013 64 bit
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]H[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]I[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]J[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]K[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]L[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​
Row​
from​
to​
Code​
Qty​
Id​
Code​
Qty​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​
1​
200​
210​
9461626177
​
1000​
200​
9461626177​
1000​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​
2​
450​
455​
9421621234​
550​
201​
9461626177​
1000​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​
3​
1020​
1030​
9461624598​
142​
202​
9461626177​
1000​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​
203​
9461626177​
1000​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​
204​
9461626177​
1000​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​
205​
9461626177​
1000​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​
206​
9461626177​
1000​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​
207​
9461626177​
1000​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR]​
208​
9461626177​
1000​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]11[/COLOR]​
209​
9461626177​
1000​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]12[/COLOR]​
210​
9461626177​
1000​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]13[/COLOR]​
450​
9421621234​
550​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]14[/COLOR]​
451​
9421621234​
550​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]15[/COLOR]​
452​
9421621234​
550​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]16[/COLOR]​
453​
9421621234​
550​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]17[/COLOR]​
454​
9421621234​
550​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]18[/COLOR]​
455​
9421621234​
550​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]19[/COLOR]​
1020​
9461624598​
142​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]20[/COLOR]​
1021​
9461624598​
142​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]21[/COLOR]​
1022​
9461624598​
142​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]22[/COLOR]​
1023​
9461624598​
142​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]23[/COLOR]​
1024​
9461624598​
142​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]24[/COLOR]​
1025​
9461624598​
142​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]25[/COLOR]​
1026​
9461624598​
142​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]26[/COLOR]​
1027​
9461624598​
142​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]27[/COLOR]​
1028​
9461624598​
142​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]28[/COLOR]​
1029​
9461624598​
142​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]29[/COLOR]​
1030​
9461624598​
142​

<tbody>
</tbody>
Sheet: Page1 (2)

<tbody>
</tbody>
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
your main table tells us that 200 through to 210 have the same code and quantity, is it vital that you list out 11 rows giving you identical codes and quantity - if so, where is the data that is used to make your main table, as perhjaps that could be used to give you the required output ?
 
Upvote 0
oldbrewer said:
your main table tells us that 200 through to 210 have the same code and quantity, is it vital that you list out 11 rows giving you identical codes and quantity - if so, where is the data that is used to make your main table, as perhjaps that could be used to give you the required output ?
Click to expand...

Sorry ... I think some part of html codes deleted .

this is raw data and result"

Excel 2013 64 bit
ABCDEFGHIJKL
1Result
2RowfromtoCodeQtyIdCodeQty
312002109461626177100020094616261771000
42450455942162123455020194616261771000
5310201030946162459814220294616261771000
620394616261771000
720494616261771000
820594616261771000
920694616261771000
1020794616261771000
1120894616261771000
1220994616261771000
1321094616261771000
144509421621234550
154519421621234550
164529421621234550
174539421621234550
184549421621234550
194559421621234550
2010209461624598142
2110219461624598142
2210229461624598142
2310239461624598142
2410249461624598142
2510259461624598142
2610269461624598142
2710279461624598142
2810289461624598142
2910299461624598142
3010309461624598142

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Page1 (2)​
 
Upvote 0
Try

Formula in J2 copied down
=IF(K2="","",INDEX(B$2:B$4,MATCH(K2,D$2:D$4,0))+COUNTIF(K$2:K2,K2)-1)

Array formula in K2 copied down
=IFERROR(INDEX(D$2:D$4,MATCH(FALSE,COUNTIF(K$1:K1,D$2:D$4)=C$2:C$4-B$2:B$4+1,0)),"")
confirmed with Ctrl+Shift+Enter, not just Enter

Formula in L2 copied down
=IF(K2="","",VLOOKUP(K2,D:E,2,0))

Hope this helps.

M.
 
Upvote 0
The solution above assumes headers in row 1 and data beginning in row 2 like ...


A​
B​
C​
D​
E​
1​
Row​
from​
to​
Code​
Qty​
2​
1​
200​
210​
9461626177​
1000​
3​
2​
450​
455​
9421621234​
550​
4​
3​
1020​
1030​
9461624598​
142​
5​


M.
 
Upvote 0
Thank a lot for your Formula .

How can i create result table with vba ?

Because i get a report once a week from Accounting Software with difference rows . I write a simple macro that extract from and to column from one of the report's column and need to create result table with Vba .

Thanks in advance .
 
Last edited:
Upvote 0
Marcelo Branco i have started new thread regarding overlapping time, please help me. its very urgent, my boss will fire me if i didnt complete by Monday
I need Overlapping time for the same entries on same day
 
Upvote 0
so if entries are 0900, 1100, 1300, 1900

do you want 1900 minus 0900 or each overlap?
 
Upvote 0
oldbrewer said:
so if entries are 0900, 1100, 1300, 1900

do you want 1900 minus 0900 or each overlap?
Click to expand...

this is a sample data . All the IDs in real data have 10 characters like this :

Excel 2013 64 bit
AB
1fromto
210008457861000852823
310004376001000438149
410002765261000276667
510002766681000276743

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1​
 
Upvote 0
I would help you if I knew what you wanted - post #9 is just some ids and you did not address my query in post #8
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA - get alphanumeric code using date in column header
Replies
3
Views
936
ajm
A
E
  • Question
VBA comment cells according to its value, based on conditions
Replies
0
Views
917
excelquestion11
E
Jerry Sullivan
  • Question
VBA anomaly when filtering table from Worksheet_Calculate
2
Replies
13
Views
2K
Jerry Sullivan
Jerry Sullivan
C
  • Question
VBA Macros to Copy Specific Cells Values from Multiple Sheets to Specific Cells in Summary Sheets
Replies
2
Views
292
chittunoo
C
J
  • Question
Multiple lookups to return a value
Replies
3
Views
369
AlanY
A

Forum statistics

Threads
1,214,981
Messages
6,122,566
Members
449,089
Latest member
Motoracer88

Share this page

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
Back
Top