create 3 sheet which has records look like having many to many relationship

%3CLINGO-SUB%20id%3D%22lingo-sub-2890693%22%20slang%3D%22en-US%22%3Ecreate%203%20sheet%20which%20has%20records%20look%20like%20having%20many%20to%20many%20relationship%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2890693%22%20slang%3D%22en-US%22%3E%3CP%3Etable%201%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%2271px%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2263px%22%20height%3D%2220%22%3Eitem%20no%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2263px%22%20height%3D%2220%22%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2263px%22%20height%3D%2220%22%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2263px%22%20height%3D%2220%22%3E3%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Etable%202%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%2264%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%20height%3D%2220%22%3EName%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3Ea%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3Eb%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3Ec%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eexpected%20table%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%22128%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%20height%3D%2220%22%3Eitem%20no%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EName%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3E1%3C%2FTD%3E%3CTD%3Ea%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3E1%3C%2FTD%3E%3CTD%3Eb%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3E1%3C%2FTD%3E%3CTD%3Ec%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3E2%3C%2FTD%3E%3CTD%3Ea%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3E2%3C%2FTD%3E%3CTD%3Eb%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3E2%3C%2FTD%3E%3CTD%3Ec%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3E3%3C%2FTD%3E%3CTD%3Ea%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3E3%3C%2FTD%3E%3CTD%3Eb%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3E3%3C%2FTD%3E%3CTD%3Ec%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPLease%20let%20me%20know%20how%20can%20be%20this%20implemented%3F%20is%20there%20any%20automated%20way%20to%20do%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2890693%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2890829%22%20slang%3D%22en-US%22%3ERe%3A%20create%203%20sheet%20which%20has%20records%20look%20like%20having%20many%20to%20many%20relationship%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2890829%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1197552%22%20target%3D%22_blank%22%3E%40Amit_Pathare1491%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20added%20both%20a%20formula(D2%3AD10)%26amp%3B(E1%3AE10)%20and%20a%20macro(shall%20run%20in%20G%3AH).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIF(COUNTIF(%24D%242%3AD2%2CD2)%26lt%3B%3D3%2CD2%2CD2%2B1)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

table 1            

item no
1
2
3

 

table 2

Name
a
b
c

 

expected table

item noName
1a
1b
1c
2a
2b
2c
3a
3b
3c

 

PLease let me know how can be this implemented? is there any automated way to do this?

2 Replies

@Amit_Pathare1491 

I have added both a formula(D2:D10)&(E1:E10) and a macro(shall run in G:H).

 

IF(COUNTIF($D$2:D2,D2)<=3,D2,D2+1)

@Amit_Pathare1491 

As variant wit Power Query - query both, add one to another as custom column and expand

image.png