Avoid Circular Ref

[ShalDRH]

Hello! I have a data set that comes as an ugly output from another system (I'm unable to adjust this output.) I need to extract a UID for each row that will group multiple rows together. Sample set below.

Here is the issue- The UID value shows up in the middle of its data set. Circular Refs for all of my attempts at a solution.

All of the UIDs will begin with a "56". Additionally, there are rows that begin with 56 but are not UIDs. I tried to "ignore" those 56 but telling it to find the word "blind" and keep looking down instead of equaling the value.

I've tried to trick the formula into working in a ton of ways, but I can't figure out a way for the cells that are AFTER the BUID to read up, while all the others read down to find it. I can't avoid the circular reference.

My example formula here has stripped out all of my convoluted attempts so its less for you to sort thru (and to save me the embarrassment of my attempts at a bandaid!)

Thanks so much for your help and thoughts to set this up!
Shalon

Row identifier formula.xlsm
	A	B	C	D	E
1	XXX
2	Purchasing Cost Summary Report by Lot Date . . . . 2/02/22
3	Time . . . . 17:47:43
4	11.24.11			Formula
5	As of 02/02/22
6	XXX			A18- First BUID- begins with 56
7	XXX			A23- also starts with 56 but isn't a BUID
8				Rows 19-25 belong with BUID at A18
9	XXX
10	Code Description
11	Lot Costs		Desired Output	BUID	Cost Code
12	40012.01 Foundation Staking		560430041	560430041	40012.01
13	40020.01 Inspection 1		560430041	560430041	40020.01
14	40020.11 Engineering Fees 1		560430041	560430041	40020.11
15	45530.01 Flatwork Labor/Turnkey 1		560430041	560430041	45530.01
16	45550.01 FY Landscape & Irrigation		560430041	560430041	45550.01
17	45580.01 Window Treatments		560430041	560430041	45580.01
18	560430041 8094 Aldred Way		560430041	560430041	560430041 8094 Aldred Way
19	Code Buyer Options		560430041	560430043	Code Buyer Options
20	13020015 Kitchen Sink - Standard		560430041	560430043	13020015 Kitchen Sink - Standard
21	39001005 Lam:Mohawk Rustic Hollow DRW12		560430041	560430043	39001005 Lam:Mohawk Rustic Hollow DRW12
22	45002092 Design Finish Package 1		560430041	560430043	45002092
23	56001005 A Better Blind 2" Faux Wood Co		560430041	560430043	56001005 A Better Blind 2" Faux Wood Co
24	81000051 Solar PPA		560430041	560430043	81000051 Solar PPA
25	OPTION TOTAL		560430041	560430043	OPTION TOTAL
26	Code Description		560430043	560430043	Code Description
27	Lot Costs		560430043	560430043	Lot Costs
28	40012.01 Foundation Staking		560430043	560430043	40012.01
29	40020.01 Inspection 1		560430043	560430043	40020.01
30	40020.11 Engineering Fees 1		560430043	560430043	40020.11
31	45330.01 Appliances Complete		560430043	560430043	45330.01
32	45520.01 Fencing		560430043	560430043	45520.01
33	45530.01 Flatwork Labor/Turnkey 1		560430043	560430043	45530.01
34	45550.01 FY Landscape & Irrigation		560430043	560430043	45550.01
35	45580.01 Window Treatments		560430043	560430043	45580.01
36	560430043 8082 Aldred Way		560430043	560430043	560430043 8082 Aldred Way
37	Code Buyer Options		560430043	567650105	Code Buyer Options
38	13020015 Kitchen Sink - Standard		560430043	567650105	13020015 Kitchen Sink - Standard
39	17000110 Cabinets - Tahoe Color: Painte		560430043	567650105	17000110 Cabinets - Tahoe Color: Painte
40	39001005 Lam:Mohawk Rustic Hollow DRW12		560430043	567650105	39001005 Lam:Mohawk Rustic Hollow DRW12
41	45002092 Design Finish Package 1		560430043	567650105	45002092
42	56001005 A Better Blind 2" Faux Wood Co		560430043	567650105	56001005 A Better Blind 2" Faux Wood Co
43	81000020 Solar Lease		560430043	567650105	81000020 Solar Lease
44	OPTION TOTAL		560430043	567650105	OPTION TOTAL
45	Code Description		567650105	567650105	Code Description
46	Lot Costs		567650105	567650105	Lot Costs
47	40012.01 Foundation Staking		567650105	567650105	40012.01
48	40020.01 Inspection 1		567650105	567650105	40020.01
49	40020.11 Engineering Fees 1		567650105	567650105	40020.11
50	40021.01 Electric Utilities		567650105	567650105	40021.01
51	45530.01 Flatwork Labor/Turnkey 1		567650105	567650105	45530.01
52	45550.01 FY Landscape & Irrigation		567650105	567650105	45550.01
53	567650105 6714 Batten Road		567650105	567650105	567650105 6714 Batten Road
54	Code Buyer Options		567650105	0	Code Buyer Options
55	13020015 Kitchen Sink - Standard		567650105	0	13020015 Kitchen Sink - Standard
56	17000120 Cabinets - Ellis Color: White		567650105	0	17000120 Cabinets - Ellis Color: White
57	35001015 Carpet- Chestnut Terrace DR389		567650105	0	35001015 Carpet- Chestnut Terrace DR389
58	39001005 Lam:Mohawk Rustic Hollow DRW12		567650105	0	39001005 Lam:Mohawk Rustic Hollow DRW12
59	45002092 Design Finish Package 1		567650105	0	45002092
60	81000020 Solar Lease		567650105	0	81000020 Solar Lease
61	OPTION TOTAL		567650105	0	OPTION TOTAL
3 SA0001

Cell Formulas
Range	Formula
D12:D61	D12	=IF(E12="","",IF(ISNUMBER(SEARCH("blind",E12)),D13,IF(LEFT(E12,2)="56",LEFT(E12,9),D13)))
E12:E61	E12	=IF(LEFT('3 SA0001'!$A12,1)="","",IF(LEFT('3 SA0001'!$A12,1)="4",LEFT('3 SA0001'!$A12,8),'3 SA0001'!$A12))

 

[Toadstool]

Hi ShalDRH,

I'm struggling to follow what you want to do. You didn't include your formula with a circular reference so I can't help diagnose but I do see a "Desired Output" column so please tell me if my guesswork is helpful.

If it is then as I'm Excel 2016 you may find other forum members can provide a better 365 solution.

ShalDRH.xlsx
	A	B	C	D	E
1	XXX
2	Purchasing Cost Summary Report by Lot Date 2/02/22
3	Time . . . . 17:47:43
4	11.24.11			Formula
5	As of 02/02/22
6	XXX			A18- First BUID- begins with 56
7	XXX			A23- also starts with 56 but isn't a BUID
8				Rows 19-25 belong with BUID at A18
9	XXX
10	Code Description
11	Lot Costs		Desired Output	BUID	Cost Code
12	40012.01 Foundation Staking		560430041	560430041	40012.01
13	40020.01 Inspection 1		560430041	560430041	40020.01
14	40020.11 Engineering Fees 1		560430041	560430041	40020.11
15	45530.01 Flatwork Labor/Turnkey 1		560430041	560430041	45530.01
16	45550.01 FY Landscape & Irrigation		560430041	560430041	45550.01
17	45580.01 Window Treatments		560430041	560430041	45580.01
18	560430041 8094 Aldred Way		560430041	560430041	560430041 8094 Aldred Way
19	Code Buyer Options		560430041	560430041	Code Buyer Options
20	13020015 Kitchen Sink - Standard		560430041	560430041	13020015 Kitchen Sink - Standard
21	39001005 Lam:Mohawk Rustic Hollow DRW12		560430041	560430041	39001005 Lam:Mohawk Rustic Hollow DRW12
22	45002092 Design Finish Package 1		560430041	560430041	45002092
23	56001005 A Better Blind 2" Faux Wood Co		560430041	560430041	56001005 A Better Blind 2" Faux Wood Co
24	81000051 Solar PPA		560430041	560430041	81000051 Solar PPA
25	OPTION TOTAL		560430041	560430041	OPTION TOTAL
26	Code Description		560430043	560430043	Code Description
27	Lot Costs		560430043	560430043	Lot Costs
28	40012.01 Foundation Staking		560430043	560430043	40012.01
29	40020.01 Inspection 1		560430043	560430043	40020.01
30	40020.11 Engineering Fees 1		560430043	560430043	40020.11
31	45330.01 Appliances Complete		560430043	560430043	45330.01
32	45520.01 Fencing		560430043	560430043	45520.01
33	45530.01 Flatwork Labor/Turnkey 1		560430043	560430043	45530.01
34	45550.01 FY Landscape & Irrigation		560430043	560430043	45550.01
35	45580.01 Window Treatments		560430043	560430043	45580.01
36	560430043 8082 Aldred Way		560430043	560430043	560430043 8082 Aldred Way
37	Code Buyer Options		560430043	560430043	Code Buyer Options
38	13020015 Kitchen Sink - Standard		560430043	560430043	13020015 Kitchen Sink - Standard
39	17000110 Cabinets - Tahoe Color: Painte		560430043	560430043	17000110 Cabinets - Tahoe Color: Painte
40	39001005 Lam:Mohawk Rustic Hollow DRW12		560430043	560430043	39001005 Lam:Mohawk Rustic Hollow DRW12
41	45002092 Design Finish Package 1		560430043	560430043	45002092
42	56001005 A Better Blind 2" Faux Wood Co		560430043	560430043	56001005 A Better Blind 2" Faux Wood Co
43	81000020 Solar Lease		560430043	560430043	81000020 Solar Lease
44	OPTION TOTAL		560430043	560430043	OPTION TOTAL
45	Code Description		567650105	567650105	Code Description
46	Lot Costs		567650105	567650105	Lot Costs
47	40012.01 Foundation Staking		567650105	567650105	40012.01
48	40020.01 Inspection 1		567650105	567650105	40020.01
49	40020.11 Engineering Fees 1		567650105	567650105	40020.11
50	40021.01 Electric Utilities		567650105	567650105	40021.01
51	45530.01 Flatwork Labor/Turnkey 1		567650105	567650105	45530.01
52	45550.01 FY Landscape & Irrigation		567650105	567650105	45550.01
53	567650105 6714 Batten Road		567650105	567650105	567650105 6714 Batten Road
54	Code Buyer Options		567650105	567650105	Code Buyer Options
55	13020015 Kitchen Sink - Standard		567650105	567650105	13020015 Kitchen Sink - Standard
56	17000120 Cabinets - Ellis Color: White		567650105	567650105	17000120 Cabinets - Ellis Color: White
57	35001015 Carpet- Chestnut Terrace DR389		567650105	567650105	35001015 Carpet- Chestnut Terrace DR389
58	39001005 Lam:Mohawk Rustic Hollow DRW12		567650105	567650105	39001005 Lam:Mohawk Rustic Hollow DRW12
59	45002092 Design Finish Package 1		567650105	567650105	45002092
60	81000020 Solar Lease		567650105	567650105	81000020 Solar Lease
61	OPTION TOTAL		567650105	567650105	OPTION TOTAL
3 SA0001

Cell Formulas
Range	Formula
D12:D61	D12	=LEFT(INDEX($A$10:$A$9999,AGGREGATE(15,6,ROW($A$10:$A$9999)-ROW($A$9)/((LEFT($A$10:$A$9999,2)="56")*(ISERR(SEARCH("blind",$A$10:$A$9999)))),COUNTIF($A$10:$A12,"Code Description"))),9)
E12:E61	E12	=IF(LEFT('3 SA0001'!$A12,1)="","",IF(LEFT('3 SA0001'!$A12,1)="4",LEFT('3 SA0001'!$A12,8),'3 SA0001'!$A12))

 

[ShalDRH]

Hi ShalDRH,

I'm struggling to follow what you want to do. You didn't include your formula with a circular reference so I can't help diagnose but I do see a "Desired Output" column so please tell me if my guesswork is helpful.

If it is then as I'm Excel 2016 you may find other forum members can provide a better 365 solution.

ShalDRH.xlsx
	A	B	C	D	E
1	XXX
2	Purchasing Cost Summary Report by Lot Date 2/02/22
3	Time . . . . 17:47:43
4	11.24.11			Formula
5	As of 02/02/22
6	XXX			A18- First BUID- begins with 56
7	XXX			A23- also starts with 56 but isn't a BUID
8				Rows 19-25 belong with BUID at A18
9	XXX
10	Code Description
11	Lot Costs		Desired Output	BUID	Cost Code
12	40012.01 Foundation Staking		560430041	560430041	40012.01
13	40020.01 Inspection 1		560430041	560430041	40020.01
14	40020.11 Engineering Fees 1		560430041	560430041	40020.11
15	45530.01 Flatwork Labor/Turnkey 1		560430041	560430041	45530.01
16	45550.01 FY Landscape & Irrigation		560430041	560430041	45550.01
17	45580.01 Window Treatments		560430041	560430041	45580.01
18	560430041 8094 Aldred Way		560430041	560430041	560430041 8094 Aldred Way
19	Code Buyer Options		560430041	560430041	Code Buyer Options
20	13020015 Kitchen Sink - Standard		560430041	560430041	13020015 Kitchen Sink - Standard
21	39001005 Lam:Mohawk Rustic Hollow DRW12		560430041	560430041	39001005 Lam:Mohawk Rustic Hollow DRW12
22	45002092 Design Finish Package 1		560430041	560430041	45002092
23	56001005 A Better Blind 2" Faux Wood Co		560430041	560430041	56001005 A Better Blind 2" Faux Wood Co
24	81000051 Solar PPA		560430041	560430041	81000051 Solar PPA
25	OPTION TOTAL		560430041	560430041	OPTION TOTAL
26	Code Description		560430043	560430043	Code Description
27	Lot Costs		560430043	560430043	Lot Costs
28	40012.01 Foundation Staking		560430043	560430043	40012.01
29	40020.01 Inspection 1		560430043	560430043	40020.01
30	40020.11 Engineering Fees 1		560430043	560430043	40020.11
31	45330.01 Appliances Complete		560430043	560430043	45330.01
32	45520.01 Fencing		560430043	560430043	45520.01
33	45530.01 Flatwork Labor/Turnkey 1		560430043	560430043	45530.01
34	45550.01 FY Landscape & Irrigation		560430043	560430043	45550.01
35	45580.01 Window Treatments		560430043	560430043	45580.01
36	560430043 8082 Aldred Way		560430043	560430043	560430043 8082 Aldred Way
37	Code Buyer Options		560430043	560430043	Code Buyer Options
38	13020015 Kitchen Sink - Standard		560430043	560430043	13020015 Kitchen Sink - Standard
39	17000110 Cabinets - Tahoe Color: Painte		560430043	560430043	17000110 Cabinets - Tahoe Color: Painte
40	39001005 Lam:Mohawk Rustic Hollow DRW12		560430043	560430043	39001005 Lam:Mohawk Rustic Hollow DRW12
41	45002092 Design Finish Package 1		560430043	560430043	45002092
42	56001005 A Better Blind 2" Faux Wood Co		560430043	560430043	56001005 A Better Blind 2" Faux Wood Co
43	81000020 Solar Lease		560430043	560430043	81000020 Solar Lease
44	OPTION TOTAL		560430043	560430043	OPTION TOTAL
45	Code Description		567650105	567650105	Code Description
46	Lot Costs		567650105	567650105	Lot Costs
47	40012.01 Foundation Staking		567650105	567650105	40012.01
48	40020.01 Inspection 1		567650105	567650105	40020.01
49	40020.11 Engineering Fees 1		567650105	567650105	40020.11
50	40021.01 Electric Utilities		567650105	567650105	40021.01
51	45530.01 Flatwork Labor/Turnkey 1		567650105	567650105	45530.01
52	45550.01 FY Landscape & Irrigation		567650105	567650105	45550.01
53	567650105 6714 Batten Road		567650105	567650105	567650105 6714 Batten Road
54	Code Buyer Options		567650105	567650105	Code Buyer Options
55	13020015 Kitchen Sink - Standard		567650105	567650105	13020015 Kitchen Sink - Standard
56	17000120 Cabinets - Ellis Color: White		567650105	567650105	17000120 Cabinets - Ellis Color: White
57	35001015 Carpet- Chestnut Terrace DR389		567650105	567650105	35001015 Carpet- Chestnut Terrace DR389
58	39001005 Lam:Mohawk Rustic Hollow DRW12		567650105	567650105	39001005 Lam:Mohawk Rustic Hollow DRW12
59	45002092 Design Finish Package 1		567650105	567650105	45002092
60	81000020 Solar Lease		567650105	567650105	81000020 Solar Lease
61	OPTION TOTAL		567650105	567650105	OPTION TOTAL
3 SA0001

Cell Formulas
Range	Formula
D12:D61	D12	=LEFT(INDEX($A$10:$A$9999,AGGREGATE(15,6,ROW($A$10:$A$9999)-ROW($A$9)/((LEFT($A$10:$A$9999,2)="56")*(ISERR(SEARCH("blind",$A$10:$A$9999)))),COUNTIF($A$10:$A12,"Code Description"))),9)
E12:E61	E12	=IF(LEFT('3 SA0001'!$A12,1)="","",IF(LEFT('3 SA0001'!$A12,1)="4",LEFT('3 SA0001'!$A12,8),'3 SA0001'!$A12))

Hello Toadstool, Thanks for the reply and for trying to decipher what I needed. I appreciate that!

Your solution works for my data sample, but not when I apply it to the larger data set. I believe because your formula, which looks for "code description", had no way to know that my larger data set has that phrase on another row within the grouping.

So here is me attempting to give you better info I hope it makes sense. And isn't too long-winded...

I can't get enough data to copy here to give you a full set, but this list at the end is pretty accurate for the output pattern. (If there is a way to attach an xls doc, I couldn't find it. Is there?) The pattern of this grouping would repeat in general (give or take a few extra/less 4xxxx.xx codes mixed into each grouping if used.) My color-coding and general explanation below-

Row identifier formula v2.xlsm
	C
5	Pattern of data
6	A91- First BUID- (begins with 56) Rows 12-104 are part of this BUID group.
7	A102- also starts with 56 but isn't a BUID, so was messing up my formula and I tried to carve it out with finding the word "blind" and ignoring the cell.
8	Rows 92-104 are the "afters" that come after the BUID but belong to the group.
3 SA0001

This column below (of the dump) has their codes and descriptions smooshed together and the "Job #" for each cost code is buried rows down and in the middle of the group that it belongs to. I'm trying to get the right job # identified for each row, and isolate the code in another column so they can just filter to their jobs and code to review the rest of the columns to the right (not included).

Due to the limit of data I can copy, I couldn't get my formula in. But the text I've been using is-

• =IF(A12="","",IF(OR(ISNUMBER(SEARCH("blind",A12)),ISNUMBER(SEARCH("option",A12))),B11,IF(LEFT(A12,4)="8100",B11,IF(LEFT(A12,2)="56",LEFT(A12,9),B13))))
• The circular reference happens at the sequence that is shown in the example data at rows 101/102. Those two fields in each grouping are circling!

Side note- my formula will populate the first UID (560430041) in ALL of the "after the UID" cells when I double click to auto-populate. Like every set of the "after" cells are that value. Like row 4000 something will still say 560430041 for those "blue" pattern cells. Or they will be blank. I haven't figured out why it does one over the other. And if activate those cells at all they turn into zeros instead of the right/wrong answer. I have no idea why that is happening. Obviously, I'm working above my skill level!

Thanks again for your help.

Row identifier formula v2.xlsm
	A
10	Code Description
11	Lot Costs
12	40012.01 Foundation Staking
13	40020.01 Inspection 1
14	40020.11 Engineering Fees 1
15	40021.01 Electric Utilities
16	40021.02 Dry House Utility Connection
17	40021.03 Sewer and Water Utilities
18	40023.01 Finish Grade
19	40023.02 Final Grade
20	40023.04 Dirt Off Haul
21	40025.01 Slab Form & Trench
22	40025.03 Slab Pour
23	40030.01 Termite Treatment
24	42010.01 1st Floor Wall
25	42010.03 Roof Sheathing - Carpentry
26	42010.04 Framing Labor Complete
27	42011.01 1st Floor Lumber
28	42015.03 Roof Trusses
29	42030.01 Clean Site - First Draw
30	42030.02 Clean Site - Second Draw
31	42030.03 Clean Site - Final Draw
32	42051.02 Ext Door Materials
33	42110.01 Window Installation-Labo
34	42110.89 Window Labor/Trnky Extras
35	42111.01 Windows Materials
36	42111.89 Window Material Extras
37	42170.01 Plumbing Slab Rough
38	42170.02 Plumbing Top Out
39	42170.03 Plumbing Final
40	42170.89 Plumbing Extras
41	42190.01 HVAC Rough
42	42190.02 HVAC Final
43	42190.03 HVAC-Compressor
44	42220.01 Electrical Rough
45	42220.02 Electrical Final
46	42240.32 Home Automation 2
47	42260.01 Fire Sprinkler Sys Rough
48	42260.02 Fire Sprinkler Sys Final
49	43040.11 Masonry Veneer Complete
50	43070.01 Stucco Lath
51	43070.02 Stucco Brown
52	43070.03 Stucco Complete
53	44010.01 Roof Loaded
54	44010.02 Roof Laid
55	44010.03 Roof Complete
56	44050.01 Gutters & Downspouts
57	44070.03 Insulation - Wall
58	44070.04 Insulation - Attic
59	44080.01 Hang Drywall
60	44080.02 Drywall Texture
61	44081.01 Stock - Drywall
62	45000.01 Cabinets Labor
63	45001.01 Cabinet Materials
64	45020.04 Kitchen Tops-Granite
65	45025.02 Vanity Tops-Cultured Marble
66	45030.01 Install Exterior Doors
67	45030.02 Install Int. Doors & Trim
68	45030.03 Install Hardware
69	45031.01 Interior Doors
70	Code Description
71	45100.20 Cultured Marble Walls
72	45110.01 Paint Exterior
73	45110.02 Paint Interior
74	45110.03 Paint Touch Up
75	45150.01 Garage Door
76	45160.01 Garage Door Opener
77	45200.01 Finish Hardware Material-1
78	45240.01 Mirrors Bath
79	45250.01 Shower Enclosure
80	45270.01 Rough Interior Clean
81	45270.02 Final Interior Clean
82	45270.03 Powder Clean
83	45300.01 Hardwood Flooring
84	45310.01 Carpet
85	45320.01 Light Fixtures
86	45330.01 Appliances Complete
87	45520.01 Fencing
88	45530.01 Flatwork Labor/Turnkey 1
89	45550.01 FY Landscape & Irrigation
90	45580.01 Window Treatments
91	560430041 8094 Aldred Way
92	Code Buyer Options
93	13020015 Kitchen Sink - Standard
94	17000110 Cabinets - Tahoe Color: Painte
95	18000025 Bath Vanity - Piedrafina Color
96	18000045 Kitchen - Silestone Quartz Col
97	25700015 Included Gas Range Pkg: SS F
98	35001005 Carpet Pad - Mohawk 3/8" 5lb P
99	35001015 Carpet- Chestnut Terrace DR389
100	39001005 Lam:Mohawk Rustic Hollow DRW12
101	45002092 Design Finish Package 1
102	56001005 A Better Blind 2" Faux Wood Co
103	81000051 Solar PPA
104	OPTION TOTAL
3 SA0001

 

[Toadstool]

I'm still not following what the rules are for sectioning each Job#.

You can upload to Google Drive (make sure to set it as share) or use Dropbox to share your spreadsheet but I'd do so as xlsx rather than xlsm.

I still think a different approach using a range for each Job would be best, then use INDIRECT or OFFSET to process each Job. The numbers don't reflect your data but this formula should help identify where each range sits.

ShalDRH.xlsx
	B
1	1st row
2	82
3	101
4	121
5
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=IFERROR(AGGREGATE(15,6,ROW($A$1:$A$9999)-ROW($A$9)/((LEFT($A$1:$A$9999,2)="56")*(ISERR(SEARCH("blind",$A$1:$A$9999)))),ROW()-ROW($B$1)),"")

 

[ShalDRH]

Thanks for your help. I'll try to make that formula work.